Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Mr_Glister
Advocate II
Advocate II

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!

 

 

Capture.PNG

1 ACCEPTED SOLUTION

@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.

 

 

View solution in original post

5 REPLIES 5
Datatouille
Solution Sage
Solution Sage

Hi @Mr_Glister

 

You can remove your calc column and use MedianX function.

 

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

And your median measure is:

Med = MedianX ( YourTable , [Total amount] )

 

This measure uses context transition.

 

 

hi @Datatouille

 

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:

Capture2.PNG

@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!

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 !

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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