由Azure数据仓库提供支持的Power BI中基于时间的细分 [英] Time-based drilldowns in Power BI powered by Azure Data Warehouse
问题描述
我设计了一个简单的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轴表示小时），以及按特定产品进行过滤的能力ct：
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
何时我需要的是以下内容：
When what I need is the following:
(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:
https://powerbi.microsoft.com/zh-CN/documentation/powerbi-desktop-tutorial-create-measures/
这篇关于由Azure数据仓库提供支持的Power BI中基于时间的细分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
