How to get the median total cost for each category? Fact Table with Category, and 1 - * reln to Cost

[j_hoyt]

I've got a data model that's roughly as shown below. 

I want a table visual with a Category Column and a Median Total Cost column, and I'm not quite sure how to write the DAX to get the right calculation.

If I have a measure like Median(Cost Table[Cost Amount]), it's going to give me the median of every individual cost amount. But in this view, I don't want a Fact with 1000 rows of $1 to be different from a Fact with 1 row of $1000. I'm only interested in the median of the Total Cost per Fact.  

[v-nuoc-msft · Accepted solution]

Hi @j_hoyt 

For your question, here is the method I provided:

Here's some dummy data

"Fact Table"

"Cost Table"

Create measures. Results were obtained by determining whether the ranking was equal to the median.

rank = 
RANKX(
    FILTER(
        ALL('Fact Table'), 
        'Fact Table'[Category] = MAX('Fact Table'[Category])
    ), 
    CALCULATE(SELECTEDVALUE('Fact Table'[Fact ID])),
    ,
    ASC,
    Dense
)

Median Cost = MEDIANX(
    FILTER(
        ALL('Fact Table'),
        'Fact Table'[Category] = MAX('Fact Table'[Category])
    ),
    'Fact Table'[rank]
)

result = 
var _id = IF('Fact Table'[rank] = 'Fact Table'[Median Cost], SELECTEDVALUE('Fact Table'[Fact ID]), BLANK())
RETURN
SUMX(
    FILTER(
        ALL('Cost Table'), 
        'Cost Table'[Fact ID] = _id
    ), 
    'Cost Table'[Cost Amount]
)

Note that the median type is whole number.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samratpbi]

Hi, looking into your description, wondering do you need sum of the values for each fact id? If so, then use SUM function instead of the median, SUM(Cost Table[Cost Amount]).

If not, can you please provide some sample data and what you want to see?

If this resolves your problem, then please mark it as solution, Thanks!

[j_hoyt]

I can't post any real data because of confidentiality, but it's something like: 

So for Category 1, the three total costs per Fact are 100, 150, and 2000, so the median should be displayed as 150. 

If I used Sum(Cost Amount), i'd get 2250, which is not what I want.