由 Azure 数据仓库提供支持的 Power BI 中基于时间的向下钻取

本文介绍了由 Azure 数据仓库提供支持的 Power BI 中基于时间的向下钻取的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我设计了一个简单的 Azure 数据仓库，我想在其中定期跟踪我的产品库存.此外，我希望能够查看按月、周、日和小时分组的数据，并能够从上到下向下钻取.我定义了 3 个维度:

I have designed a simple Azure Data Warehouse where I want to track stock of my products on periodic basis. Moreover I want to have an ability to see that data grouped by month, weeks, days and hours with ability to drill down from top to bottom. I have defined 3 dimensions:

DimDate DimTime DimProduct

我还定义了一个事实表来跟踪产品库存:

I have also defined a Fact table to track product stocks:

FactStocks - DateKey (20160510, 20160511, etc) - TimeKey (0..23) - ProductKey (Product1, Product2) - StockValue (number, 1..9999)

我的事实样本数据如下:

My fact sample data is below:

20160510 20 Product1 100 20160510 20 Product2 30 20160510 21 Product1 110 20160510 21 Product2 35 20160510 22 Product1 112 20160510 22 Product2 28 20160510 23 Product1 120 20160510 23 Product2 31 20160511 00 Product1 150 20160511 00 Product2 29 20160511 01 Product1 95 20160511 01 Product2 40

我需要的是一张随时间变化的产品可用性图表，能够检查总时间(其中 x 轴代表小时数)，以及按特定产品过滤的能力:

What I need is a chart of product availability over time with ability to check total (where x axis represents hours), as well as ability to filter by specific product:

Total - 130, 145, 140, 151, 179, 135 Product1 - 100, 110, 112, 120, 150, 95; Product2 - 30, 35, 28, 31, 29, 40; x-> 20,21,22,23,00,01

此外，我需要能够按天和产品(其中 x 轴表示通过 DimDate 表提供的天、周、月、年)来钻取和浏览平均可用性:

Moreover I need an ability to drill up and browse average availability by days and products (where x axis represents days, weeks, month, years available via the DimDate table):

Total - 141.5, 157 Product1 - 110.5, 122.5 Product2 - 31, 34.5 x-> 20160510, 20160511

似乎 Power BI 无法按日期分组，因为它正在尝试使用聚合函数来获取每日值，并且无法指定平均函数，这将考虑产品(按产品分组).聚合功能在这里不起作用，Power BI 正在尝试对给定日期的所有产品的所有值求和并获得平均值(例如 20160511):

It seems that Power BI is unable to make that group by date thing, because it's trying to use an aggregate function to get the daily value and there is no ability to specify the average function which will take into account products (with grouping by product). The aggregating function just doesn't work here, Power BI is trying to sum all values across all products for a given day and get the average (e.g. for 20160511):

150+29+95+40 / 4 = 78.5

当我需要以下内容时:

(150+29) + (95+40) / 2 = 157

我只是想让它具有交互性，能够选择产品并轻松向上和向下钻取.请告知我应该如何修改仓库结构以支持我的方案.

I just want to make it interactive with an ability to chose a product and easily drill up and down. Please advise how I should modify my Warehouse structure to support my scenario.

推荐答案

我认为你不需要改变你的结构.我将在您的 FactStocks 表上创建一个新度量(使用 Power BI Desktop)，以使用 DAX 函数根据您的需要计算可用性.

I don't think you need to change your structure. I would create a New Measure on your FactStocks table (using Power BI Desktop), to calculate Availability as you require it using DAX functions.

我猜测了一下，因为除了 1 个示例之外，您还没有真正说明您的要求，但它可能看起来像这样:

I'm guessing a bit as you havent really spelled out your requirement besides 1 example, but it would probably look something like this:

可用性 = SUM([StockValue])/DISTINCTCOUNT([ProductKey])

这是有关 PBI 桌面度量的完整教程:

Here's a full tutorial on Measures in PBI Desktop:

powerbi.microsoft/zh-cn/documentation/powerbi-desktop-tutorial-create-measures/