measure to calculate a population by tiers

[Bernd]

I have a table with a population of people and a value per person. I need to build a single measure to calculate the average value by tier. The tiers should be defined by % of people sorted by value, e.g. 60% bottom, next 20% and top 20%.

Usage of a parameter table to easily adapt the numbers and size of the tiers would also be great.

How can I get this implemented in DAX?

[nickchobotar]

@Bernd

What exactly do you mean by population of people and value per person? Could you please post some sample data and the end result you expect to get.

Thanks, N -

[Bernd]

@nickchobotar  Here is an example on how it should look like:

Thx.,  Bernd

[nickchobotar]

@Bernd

Here is the solution.

Your # People and Value AVG are two simple DAX formulas and Tiers dynamic logic is returned in a calculated column.

Download Link

#People = COUNTROWS(Table1)

Average of Value = AVERAGE(Table1[Value])


Tiers (Dynamic) = 
VAR Index = 
COUNTROWS(
    FILTER(
        Table1,
        Table1[Value] < EARLIER(Table1[Value]) +1
    )
) 
RETURN
SWITCH(
    TRUE(),
    Index <=7, "Tier 1 (60%)",
    Index > 7 && Index <11, "Tier 2 (Next 20%)",
    "Tier 3 (Top 20%)"
)   

[Bernd]

Thx. @nickchobotar, your solution works very well for the static data provided. However, since the set of people may be impacted by filters, I need to calculated the people by tier dynamically by the then current filter context.

How can I do that, preferably also using a parameter table to define the tiers dynamically, e.g.

TierMinMax

1		0.6
2	0.6	0.8
3	0.8

Is there a way to adapt your solution accordingly?

[nickchobotar · Accepted solution]

@Bernd

Before we move to adding a parameter table. Try this new 2.0 version. I have updated the calculated column and give you an option to change variable values  in the bolded part below it's fully dynamic now.  With parameters table you will have to go into the model and change parameters I think this version is more user friendly.

Tiers Dynamic = 
// Change your parameters here: 
    VAR Tier1 = 0.6
    VAR Tier2 = 0.8
// ---------------------------
VAR Index = 
CALCULATE(
    SUM(Table1[Value]),
    FILTER(
        Table1,
        Table1[Value] <= EARLIER(Table1[Value])
    )
) / CALCULATE(SUM(Table1[Value]), ALL(Table1))

RETURN
SWITCH(
    TRUE(),
    Index < Tier1, "Tier 1",
    Index > Tier1 && Index <= Tier2, "Tier 2",
    "Tier 3"
)

[Bernd]

Thx. @nickchobotar. This is really great, I will mark it as a solution.

[nickchobotar]

Thank you  @Bernd

[Anonymous]

Hi @nickchobotar  I am trying to use your formula but I receive this message that is not enought memory to process, any idea about this message?. "There's not enough memory to complete this operation. Please try again later when there may be more memory available."

Tiers Dynamic =

// Change your parameters here:

VAR Tier1 = 0.6

VAR Tier2 = 0.8

// ---------------------------

VAR Index =

CALCULATE(

SUM('Sales Data'[NSB]),

FILTER(

'Sales Data',

'Sales Data'[NSB] <= EARLIER('Sales Data'[NSB])

)

) / CALCULATE(SUM('Sales Data'[NSB]), ALL('Sales Data'))

RETURN

SWITCH(

TRUE(),

Index < Tier1, "Tier 1",

Index > Tier1 && Index <= Tier2, "Tier 2",

"Tier 3"

)