Calculate Median Revenue Per Customer where Category = X

[algunn14]

Hi All,

I am trying to calculate median donation amount per donor for selected time period where the category = B using DAX.

donation_date	customer_id	category	donation_amount
9/15/2021	41	A	26
9/22/2021	7	A	55
10/1/2021	41	B	10
10/1/2021	11	B	60
10/4/2021	1	B	52
10/7/2021	7	B	69
10/12/2021	3	B	89
10/12/2021	9	B	49
10/15/2021	15	B	26
10/18/2021	27	B	32
10/21/2021	33	B	100
11/1/2021	41	B	37

Since median requires a column or table, I created the calculated column

-  CatB Revenue = if( 'donors'[category] = "B", 'donors'[donation_amount])

I then created the following measure, and placed it on a Card

- Median Donation Amount = MEDIAN('donors'[CatB Revenue])

When filtering the date from 9/15/2021 - 10/22/2021 the calc seems to work

However, when I expand the end date to 11/1/2021 the median is not returning what I want. You can see in the table on the left (screenshot below) the median is 50.5, however my desired output is median for the table on the right, where we would aggregate revenue by donor_id and find the median. I'm sure the solution is simple, but after searching through the forums, and trying several different calcs I have had no luck. Any assistance is greatly appreciated! 

[DataInsights · Accepted solution]

@algunn14,

Try these measures. This approach is dynamic, allowing you to specify the category via a slicer instead of hardcoding the category in a calculated column. The second measure creates a virtual table with the sum of each donation amount by customer id, and then MEDIANX iterates this virtual table, returning the median.

Sum Donation Amount = SUM ( donors[donation_amount] )

Median Donation Amount = 
VAR vTable =
    ADDCOLUMNS (
        VALUES ( donors[customer_id] ),
        "@DonationAmount", [Sum Donation Amount]
    )
VAR vResult =
    MEDIANX ( vTable, [@DonationAmount] )
RETURN
    vResult

[algunn14]

@DataInsights this was very helpful, I really appreciate your support!

Would it be possible to include the CatA / CatB within the calc instead of using a slicer?

[DataInsights]

@algunn14,

Glad to hear that works. Here's a measure that allows you to hardcode the category:

Median Donation Amount Cat B = 
VAR vTable =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( donors[customer_id] ),
            "@DonationAmount", [Sum Donation Amount]
        ),
        donors[category] = "B"
    )
VAR vResult =
    MEDIANX ( vTable, [@DonationAmount] )
RETURN
    vResult