Filter R12 EBIT measure with master slicer table

[alexandert]

In my example I have calculated a [R12 EBIT %] measure for a number of stores - returning a percentage ranging from -51% to +34%.

I would like to categorize these by a simple threshold table ([MasterSlicer]) looking something like this:

Label		Pct			Order
OK		0	         	1
Neutral		0,1			2
Good		0,17		        3

I can't seem to figure the correct way to do it. Any suggestions?

Thanks,

Alexander

[v-jiascu-msft]

@alexandert

Hi Alexander,

If we can find out the max "Pct", we can find out the "Label". Maybe the measure looks like this:

Label_Measure =
VAR MaxPct =
    CALCULATE (
        MAX ( 'Table1'[Pct] ),
        FILTER ( 'Table1', Table1[Pct] <= [R12 EBIT %] )
    )
RETURN
    IF (
        ISBLANK ( MaxPct ),
        "NA",
        CALCULATE ( VALUES ( 'Table1'[Label] ), 'Table1'[Pct] = MaxPct )
    )

Best Regards!

Dale

[alexandert]

Thanks Dale,

However, I get the "CALCULATE has been used in a True/False expression....." error message. Not sure what to do, ideas?

Thanks,

Alex

[v-jiascu-msft · Accepted solution]

Hi @alexandert,

Please use it as a measure. And "R12 EBIT %" is a measure that already exist.

 

Best Regards!

Dale

[alexandert]

Thanks, works like a charm!