cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mr_Glister Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Excelside Established Member
Established Member

Re: Median of distinct total per month

@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
Excelside Established Member
Established Member

Re: Median of distinct total per month

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.

 

 

Mr_Glister Regular Visitor
Regular Visitor

Re: Median of distinct total per month

hi @Excelside

 

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

Highlighted
Excelside Established Member
Established Member

Re: Median of distinct total per month

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

 

 

Mr_Glister Regular Visitor
Regular Visitor

Re: Median of distinct total per month

It was as easy as that! :-)

Thank you very much!

Excelside Established Member
Established Member

Re: Median of distinct total per month

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

 

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 !