Taking the Median of a Measure

[jegio]

Hello,

I'm new to the community and need a little help.

I have a table that has one row for each transaction, and I was able to make a Measure that counts the number of transactions pretty easily:

     CountTransactions = Count('Store1'[Index])

This works fine, and adjusts to input from the slicers. Particularly, I want to apply it so I can see the number of Transactions that each customer makes each quarter, and slicing on "CustomerName" and "Quarter" gives me just that.

What I would like now is to find the medium number of transactions that occur between customers within the store each quarter. Is there a way to use a measure to take the median of this measure?

When I try to reference the above measure using the following formula:

     MedianTransactions = Median('Store1'[CountTransactions])

I get the error "Column 'GethelpCases' in table 'Gethelp' cannot be found or may not be used in this expression."

I'm guessing that this is because I am trying to reference a Measure inside a Measure, Is there a way to do what I am trying to do?

[alanhodgson · Accepted solution]

Hey @jegio,

You could make the first COUNT function a Calculated Column instead of a Measure, then create the measure using the MEDIAN function on the calculated column.

CalculatedColumn = Count('Store1'[Index])

Measure = Median('Store1'[CalculatedColumn])

Best,

Alan

[jegio]

Hi @alanhodgson,

Thank you for taking the time to reply to me.

When I try that solution for the calculated column, I get the total number of transactions in the table instead of the number of transactions for each account. Each row shows "6000", and I think thats because PowerBI calculates all of these columns statically upon refresh instead of dynamically.

I read somewhere that when creating something that needs to respond to slicer input, it needs to be a measure. In this case, I think that the Count of Cases needs to be a measure, right?

Thanks,

Jeff

[v-shex-msft]

Hi @jegio,

It show be a measure, if you use the calculated column it will calculate all of the list.

Regards,

Xiaoxin Sheng