Solved

How to compute a median ?

  • 11 December 2023
  • 3 replies
  • 195 views
A
Rank
Userlevel 3
Badge +9

Hi,

How can I do a median in Pigment ?

Thanks

icon

Best answer by Matt D 11 December 2023, 16:00

View original

3 replies

Matt D
Rank
Userlevel 3
Badge +4

Hi Alexandre,

 

We don’t have a function for calculating the median right now, but you can calculate it by combining a few modifiers and functions in a formula like the one below:

rev2[RANK(rev2, Month, ASC, Sequential) IN (ROUNDDOWN((rev2[REMOVE COUNT: Country] + 1) / 2):ROUNDUP((rev2[REMOVE COUNT: Country] + 1) / 2))][REMOVE AVG: Country]

It might look a little complex, but see the below example to see it in action. We are calculating the median of the revenue across all countries for a given month.

 

Hopefully you can leverage this example for your own modelling :)
 

Cheers,

Matt
A
Rank
Userlevel 3
Badge +9

Hi, thank you for the tips! :)

 

J
Rank
Userlevel 2
Badge +4

Hey @Matt D thanks for this response.  I’m trying to do something similar but with slightly more dimensionality.  Using your example, let’s say the rev2 metric was structured by country, month, AND customer - what I’d like to do is calculate the median customer revenue amount by country & month.  My assumption is that I’m running into limitations with the “Group” portion of the RANK formula - in your example above it resets the ranking by the month dimension, but with what I’m doing I’d think it would need to reset rankings on month and country. 

Are you aware of any ways to accomplish something like this? 

EDIT: I believe I was able to find what I needed in the article on the RANK function, using a unique dimension property along with the “&” symbol to reset across multiple dimensions.  Thanks again for providing this example 

Thanks, 

Reply


Ask your question here!

Terms & ConditionsCookie settings