使用AVERAGE（）计算的DAX度量的MDX语法 [英] MDX syntax for DAX measures calculated with AVERAGE()

问题描述

  = CUBESET（ThisWorkbookDataModel，
 TopCount（
 [ProductBV]。[Product Name] .Children，10，
 sum（
（
 [Calendar]。[Week Ending]。[All] 6/2013]：[Calendar]。[Week Ending]。[All]。[& TEXT（$ E $ 2，m / d / yyyy）&]，
 [ProductBV] [全部]] [全部] [&$ E $ 3&] 
），
 [措施] [商品评论计数] 
）
 ），
到目前为止的十大）
  

使用以下DAX公式。

 产品评论计数：= COUNTROWS（ProductBV）
  pre> 
 
 
但是，当我将DAX度量值更改为使用AVERAGE函数（Product Avg Review）的度量值时，CUBESET函数无法正常工作。它仍然具有正确数量的项目在CUBESET，但是当我使用CUBERANKEDMEMBER（1-10），它不会显示我的平均评级前10名产品。
 产品平均评分：= AVERAGE（ProductBV [评分]）
  
 
 
 
  = CUBESET（ThisWorkbookDataModel，
TopCount（
 [ProductBV]。[产品名称]。儿童，10，
（
（
 [日历]）[周末] [全部] [1/6/2013] [日历] [周末] [全部] [& TEXT（$ E $ 2，m / d / yyyy）&]，
 [ProductBV] [审核状态（ALL） ] [全部] [&$ E $ 3&] 
），
 [措施] [产品平均评论] 
）
），
到目前为止的十大）
  
总而言之，我的数据很简单。我有一个数据表连接到包含我的产品名称，评级，日期和审查状态的日历表。 
 
 
 
在我的信息中心内，我的日期（$ E $ 2）和审查状态（$ E $ 3）可以由用户通过下拉列表更改。 
 
 
  + ----------------- + ----------- -------- + ------------ + ---------------- + 
 |提交日期|审核状态|产品编号|总体评分| 
 + ----------------- + ------------------- + ------- ----- + ---------------- + 
 | 10/23/2016 |已批准|产品1 | 5 | 
 | 10/23/2016 |已批准|产品2 | 5 | 
 | 10/23/2016 |已批准|产品3 | 5 | 
 | 10/23/2016 | REJECTED |产品3 | 3 | 
 | 10/23/2016 | REJECTED |产品4 | 3 | 
 + ----------------- + ------------------- + ------- ----- + ---------------- + 
  
任何人都可以告诉我为什么会发生这种情况以及如何解决？
解决方案
当我看到你的问题时，我想知道为什么你正在使用Excel公式和控件来创建一个仪表板，如果您已经在PowerPivot中建模。 PowerPivot允许您以更强大和可靠的方式分析和可视化数据。
 
 
 
您的初始方法非常复杂，很难考虑到：
 
 
 
 
 
每当将新产品添加到您的来源时，您必须编辑仪表板。 
 
 
 Excel函数很容易出错，因为它们期望一个特定的值作为参数，用户可以修改它导致错误。
 
 
 PowerPivot计算速度更快
 
 
您不能向用户提供与数据的很多交互。
 
 
 
 
 
 
 
 
这是使用您的PowerPivot模型的数据透视表构建的仪表板。
 
 
 
  
 
 
 
  
 
 
 
在行窗格中添加产品名称在值添加上周，本周和正面运动或负向运动（根据您正在构建的数据透视表）测量。 
 
 
 
  
 
 
 
一旦你建立了表，你有必须获得TOP X 产品名称。
 
 
 
在每个数据透视表中，单击行标记图标过滤器（以下设置为正移动数据透视表）：
 
 
 
  
 
 
 
并使用以下设置：
 
 
 
  < img src =https://i.stack.imgur.com/2oQsL.pngalt =在此输入图像说明>  
 
 
 
更改正面运动为负向运动当您过滤以获得前5名产品名称在负移动表。
 
 
 
虽然您可以在DAX中纯粹获得前五名产品名称，但我认为最好使用数据透视表过滤器选项，将其动态化为任意数量的顶级产品作为未来的新需求），而无需修改底层的DAX。
 
 
 
然后，您只需要添加条件格式即可完成。
 
 
 
让我知道这是否有帮助。 
 
I have the following expression in Excel that works fine.
=CUBESET("ThisWorkbookDataModel",
"TopCount(
[ProductBV].[Product Name].Children,10, 
sum(
(
[Calendar].[Week Ending].[All].[1/6/2013]:[Calendar].[Week Ending].[All].["&TEXT($E$2,"m/d/yyyy")&"],
[ProductBV].[Moderation Status (ALL)].[All].["&$E$3&"]
),
[Measures].[Product Review Count]
)
)",
"Top 10 to date")
The Product Review Count measure using the following DAX formula.
Product Review Count:=COUNTROWS(ProductBV)
However, when I change the DAX measure to one that utilizes an AVERAGE function (Product Avg Review), the CUBESET function does not work correctly. It still has the correct number of items in the CUBESET, but when I use CUBERANKEDMEMBER(1-10), it does not show me the top 10 Products by Average Rating.
Product Avg Review:=AVERAGE(ProductBV[Rating])
'Not working correctly' expression below:
 =CUBESET("ThisWorkbookDataModel",
"TopCount(
[ProductBV].[Product Name].Children,10, 
sum(
(
[Calendar].[Week Ending].[All].[1/6/2013]:[Calendar].[Week Ending].[All].["&TEXT($E$2,"m/d/yyyy")&"],
[ProductBV].[Moderation Status (ALL)].[All].["&$E$3&"]
),
[Measures].[Product Avg Review]
)
)",
"Top 10 to date")
All in all, my data is very simple. I have one data table connected to a calendar table that contains my product name, rating, date and review status. 

The within my dashboard, my date ($E$2) and the review status ($E$3) can be changed by the user via a dropdown.
+-----------------+-------------------+------------+----------------+
| Submission Date | Moderation Status | Product ID | Overall Rating |
+-----------------+-------------------+------------+----------------+
| 10/23/2016      | APPROVED          | Product 1  |              5 |
| 10/23/2016      | APPROVED          | Product 2  |              5 |
| 10/23/2016      | APPROVED          | Product 3  |              5 |
| 10/23/2016      | REJECTED          | Product 3  |              3 |
| 10/23/2016      | REJECTED          | Product 4  |              3 |
+-----------------+-------------------+------------+----------------+
Can anyone tell me why this is happening and how to fix it?
解决方案
When I saw your question I wondered why you were using Excel formulas and controls to create a dashboard if you had you model in PowerPivot. PowerPivot lets you analyze and visualize data in a more robust and reliable way.

Your initial approach is very complex and it is hard to mantain taking in account that:


You have to edit your dashboard whenever a new product be added to your source. 
Excel functions are prone to errors since they are expecting a specific value as parameters and user could modify it causing errors.
PowerPivot calculations are performed faster than Excel functions, specially for large datasets.
You cannot offer much interaction with the data to your user.




This is the Dashboard I'd build using Pivot Tables conected to your PowerPivot model.



DOWNLOAD THE EXCEL FILE HERE

To build this dashboard I used four DAX measures:
This Week :=
CALCULATE (
        [Product Avg Review],
        FILTER (
            ALL ( Calendar ),
            Calendar[Week Ending] <= MAX ( Calendar[Week Ending] )
        )
    )
Last Week =
    CALCULATE (
        [Product Avg Review],
        FILTER (
            ALL ( Calendar ),
            Calendar[Week Ending]
                <= MAX ( Calendar[Week Ending] ) - 7
        )
    )
Positive Movement :=
    (
        CALCULATE (
            [Product Avg Review],
            FILTER (
                ALL ( Calendar ),
                Calendar[Week Ending]
                    <= MAX ( Calendar[Week Ending] ) - 7
            )
        )
            - CALCULATE (
                [Product Avg Review],
                FILTER (
                    ALL ( Calendar ),
                    Calendar[Week Ending] <= MAX ( Calendar[Week Ending] )
                )
            )
    )
        * -1
Negative Movement :=
    CALCULATE (
        [Product Avg Review],
        FILTER (
            ALL ( Calendar ),
            Calendar[Week Ending]
                <= MAX ( Calendar[Week Ending] ) - 7
        )
    )
        - CALCULATE (
            [Product Avg Review],
            FILTER (
                ALL ( Calendar ),
                Calendar[Week Ending] <= MAX ( Calendar[Week Ending] )
            )
        )
With measures working you can create a two Pivot Tables, one for showing positive movers and other one to show negative movers.

  
Note my Excel UI is in spanish, I hope you don't get stuck with this. Just follow the instructions and search in Google, there are plenty resources about Pivot Tables.
 

In Rows pane add Product Name, in Values add Last Week, This Week and Positive Movement or Negative Movement (according to the Pivot Table you are building) measures. 



Once you have the table built you have have to get the TOP X Product Names.

In each Pivot Table click the Row tag icon filter (The below settings are for Positive Movers Pivot Table):

 

And use these settings:



Change Positive Movement for Negative Movement when you are filtering to get the Top 5 Product Names in the Negative Movement table.

While you can get the Top 5 product names purely in DAX, I think it is better to use the Pivot Table filter option, leaving it dynamic to any number of top products (what could be a new requeriment in the future) without the need to modify the underlying DAX.

Then you just need to add Conditional Formats and you are done.

Let me know if this helps.  

                        
这篇关于使用AVERAGE（）计算的DAX度量的MDX语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！