cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Median of distinct total per month

Hi,

I can't believe how much difficulty this apparently easy problem is causing me.

Below is a sample set of data. Imagine it to be a list of all sales to the customers of our company.

We see that for instance in Month 1 we had 1 sale to customer AAA, 3 sales to BBB, 1 to CCC, and 2 sales to customer DDD.

I added the calculated column Sum per month = CALCULATE(sum(Sheet1[Volume]);ALLEXCEPT(Sheet1;Sheet1[Customer Name];Sheet1[Month]))

For Month 1 it correctly gives me:

AAA = 380

BBB  = 895

CCC = 100

DDD = 388

However, the calculated column of course repeats these values in each row where the same customer shows up within the same month again.

Now all I want to do is create a measure (unless you have better ideas) that correctly calculates the Median of the customers' totals each month. So for Month 1 the correct value would be 384, for Month 2 it's 829 and for Month 3 it's 404.

I think the solution is to tell the Median to only consider the distinct values within a month. Of course I tried doing that but the results I got where never correct, even when I created a calculated table with only the distinct values. Really hope one of you can show me how easy it is and I can sleep again at night!

1 ACCEPTED SOLUTION
MVP

@Mr_Glister

Well ok sorry I hadn't taken into account the fact that the customer can appear several times in the same month.

In that case:

Med = MedianX ( Values(YourTable[Customer Name]) , [Total amount] )

will work because the grain of the iteration has been adjusted to what you want.

5 REPLIES 5
MVP

You can remove your calc column and use MedianX function.

Create a simple measure: Total Amount = Sum(YourTable[Volume] )

Med = MedianX ( YourTable , [Total amount] )

This measure uses context transition.

Thanks for the quick reply but this doesn't seem to be the correct solution.

I did what you say but this produces only the Median of the individual volumes per month.

I get in fact the same result if I only do =MEDIAN(MyTable[Volume]).

But that is not what I need. It is each customer's total per month of which I want to calculate the Median from.

Your solution produces this for me:

MVP

@Mr_Glister

Well ok sorry I hadn't taken into account the fact that the customer can appear several times in the same month.

In that case:

Med = MedianX ( Values(YourTable[Customer Name]) , [Total amount] )

will work because the grain of the iteration has been adjusted to what you want.

It was as easy as that! 🙂

Thank you very much!

MVP

The logic running behind is not as easy as the simplicity of the syntax 🙂

The mix of X functions with context transition + Values() or All () to alter the grain of your Table argument is a classic pattern that helps solve many DAX issues !

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors