cancel
Showing results for
Did you mean:
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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
Established Member

## Re: Median of distinct total per month

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.

Regular Visitor

## Re: Median of distinct total per month

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:

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

Regular Visitor

## Re: Median of distinct total per month

It was as easy as that! :-)

Thank you very much!

Established Member

## Re: Median of distinct total per month

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 !