Median of distinct total per month

[Mr_Glister]

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!

[Datatouille]

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]

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:

[Datatouille · 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.

[Mr_Glister]

It was as easy as that! 🙂

Thank you very much!

[Datatouille]

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 !