DAX类别总和的中位数 [英] DAX Median of category sums

问题描述

如何计算类别总和的中位数?我有示例数据:

How to calculate median of category sums? I have sample data:

+----------------+-----------+
|    category    |   sales   |
+----------------+-----------+
| a              | 1         |
| a              | 2         |
| a              | 4         |
| b              | 1         |
| b              | 3         |
| b              | 4         |
| c              | 1         |
| c              | 4         |
| c              | 5         |
+----------------+-----------+

+----------------+-----------+
| category       | sales_sum |
+----------------+-----------+
| a              | 7         |
| b              | 8         | <- This median 
| c              | 10        |
+----------------+-----------+
| median of sums | 8         | <- This is expected results, regardless row context
+----------------+-----------+

我在这项措施上收效甚微.它返回正确的结果，但仅用于类别总计.但我想每个类别获得8分.

I have had little success with this measure. It returns correct results but only for category total. But I want to get 8 for each category.

Median_of_sums :=
    MEDIANX (
        VALUES ( T[Category] ),
        SUM ( T[Sales] )
    )

通过上述措施，我可以得到想要的东西:

I can get what I want by referring to the above measure:

CALCULATE ( [Median_of_sums], REMOVEFILTERS ( T[Category] ) )

但是我想一口气拿下它.

But I would like to have it in one shot.

推荐答案

我不确定您要寻找的是什么，但是也许使用SUMMARIZE函数可以解决问题:

I am not entirely sure what you are looking for, but perhaps using the SUMMARIZE function would do the trick here:

Total =
MEDIANX (
    SUMMARIZE (
        T,
        T[category],
        "Sales_Calc", SUM ( T[sales] )
    ),
    [Sales_Calc]
)

其想法是首先首先在类别级别汇总信息，然后计算汇总表的中位数.对于附加的样本，这将产生以下结果:

The idea is to first summarize the information at a category level initially and then calculating the median for the summarized table. This would give the following results for the attached sample:

a     7
b     8
c     10
Total 8

如果要在所有类别中反映8，则必须使用ALL函数来确保类别上下文不会影响计算:

If you want 8 to be reflected for all categories, you would have to use the ALL function to make sure the category context does not affect the calculation:

Total =
MEDIANX (
    SUMMARIZE (
        ALL ( T ),
        T[category],
        "Sales_Calc", SUM ( T[sales] )
    ),
    [Sales_Calc]
)

希望这会有所帮助.

这篇关于DAX类别总和的中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！