类别总和的 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] )
    )

推荐答案

我不完全确定您在寻找什么，但也许在这里使用 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屋！