Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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!
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
@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?
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
Proud to be a Super User!