DAX Median of category sums

Question

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
+----------------+-----------+

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.

CR7SMS CR7SMS · Accepted Answer · 2020-02-13T16:34:12

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

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]
)

Hope this helps.

DAX Median of category sums

1 Answers