Filter by rank, then calculate formula?

[Anonymous]

Hi,

I have sales information per district with different filters about certain item classes and branch types within a district. I have margin, sales, and cost per bucket. I would like to find a "best in class" margin number based on a calculation that can filter with said item classes and branch types. This "best in class" margin  has the logic of the total margin of the top fourth margin numbers in the company. Intuitively, I would say "if(total margin > percentile(total margin, .75), divide(sum(total sales) - sum(total cost), total sales)". How would i put together this measure correctly with DAX that will filter correctly?

[Anonymous]

I thought something like this would be close at least.

Best in Class = CALCULATE([POS Margin],'Best in Class'[Total Margin]>[75th Percentile])

where POS Margin is a measure

POS Margin = divide(sum('Best in Class'[Total Sales])-sum('Best in Class'[Total Cost]),sum('Best in Class'[Total Sales]))

where Total Margin is a column,

and where [75th Percentille] is a measure

75th Percentile = PERCENTILE.INC('Best in Class'[Total Margin],.75)

 I get the error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Anonymous]

I also tried 

Best in Class = CALCULATE([POS Margin],filter('Best in Class','Best in Class'[Total Margin]>[75th Percentile]))

which didn't have an error, but is not  returning what looks like anything as far as values.

[OwenAuger]

Hi @Anonymous

I believe you can use one of the PERCENTILEX functions.

To define the measure, you just need to define the 'granularity' you want to use to calculate percentiles.

For example, do you want percentiles calculated across all item class & branch type combinations, or separately for each branch across all item classes, or something else?

Might help if you could give an example of the calculation with dummy data.

[Anonymous]

@OwenAuger

Dummy data:

Margin   Sales   Cost   Standard_Item

45%       100      55       Y

42%       120      70       Y

50%       140      70       Y

54%       130      60       Y

25%       60        45       N

40%       50        30       N

45%       100      55       N

43%       105      60       N

So I'd like to write something that ranks the margin by percentile and then calculates the new margin. So with no filter, I'd like it to take the 75th percentile of margin (50% & 54%) and then calculate the new margin in that percentile. divide(sum(sales)-sum(cost),sales). If it is filtered to a non standard item, it takes the 75th percentile of margin (45%) and calculates the margin in that percentile divide(sum(sales)-sum(cost),sales). Does this help?

[OwenAuger]

@Anonymous

I see.

Without creating any intermediate measures, and making use of Margin percentage already in the table, you could create a measure like this (rename table as needed):

Margin % for 75th percentile and above =
VAR Percentile75 =
    PERCENTILEX.INC ( YourTable, YourTable[Margin], 0.75 )
RETURN
    CALCULATE (
        1 - DIVIDE ( SUM ( YourTable[Cost] ), SUM ( YourTable[Sales] ) ),
        YourTable[Margin] >= Percentile75
    )

[Anonymous]

@OwenAuger,

Thanks for getting back to me. This still gives me the error: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

How can I get around this?

[OwenAuger · Accepted solution]

 @Anonymous

Not sure what's happened there - should be fine if you define the measure as described with the VAR/RETURN structure.

It's working in this pbix file:

https://www.dropbox.com/s/w9l7j0flmof3txj/Margin%2075th%20percentile.pbix?dl=1

Naming is slightly different from yours, but look at the measure on the card.

[Anonymous]

@OwenAuger

This worked. Thanks for your time and help. The dummy file was very helpful.