Create a measure of a measure: when greater than x then 1 else 0

[acg]

Hi 

I have some IDs for which I calculated the average time worked. 

Average overtime looks simply like this:

Average Overtime worked=

AVERAGE(timesheet[Weekly_OverTimeHours])

How can I create a new measure called 

Indicator= when [Average Overtime worked] > 5 then 1 else 0 ?

I know I could use the filter, but I need to have Indicator to create a visual subsequently. 

ID	Average Overtime worked	Indicator
1273	8	1
4476	4	0
5623	6	1
2233	5	0
4423	7	1

[amitchandak]

@acg , Create a new table like Indicator with values 1,0 and use that as slicer

Now create a measure like

calculate([Average Overtime worked]. filter(Values(Table[ID]), [Indicator] = max(Indicator[Indicator])))

Refer this, if needed

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[acg]

Does this also work for numeric outcomes? 0/1 needs to be numeric so that it can be used later on for some calculations / visuals, where the indicator needs to be counted. 

[acg]

Great @amitchandak, 

I see you create the buckets and that is how you create the indicator. 

So I created this as in:

Margin Type =

SWITCH(TRUE(),

[AVG OT Measure] > 5, 1,

[AVG OT Measure] = 5, 1,

[AVG OT Measure] <5, 0)

But it says: The expression specified in the query is not a valid table expression.

Why would that be? THe indivator 0/1 needs to be numeric, so it can be used later on. 

[v-yingjl · Accepted solution]

Hi @acg ,

The [Margin Type] formula you have created could not be used as a calculated table, it should be a measure or a calculated column.

Measure:

Margin Type = 
IF(
    [Average overtime worked] > 5,1,0
)

Calculated column:

Margin Type column =
VAR _avg =
    CALCULATE (
        AVERAGE ( timesheet[Weekly_OverTimeHours] ),
        ALLEXCEPT ( 'timesheet', timesheet[ID] )
    )
RETURN
    IF ( _avg > 5, 1, 0 )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[acg]

Hi @

Hi @v-yingjl , this works nicely, however, I need a way to sum Margin Type Coulumn. 

So I need a total value of 3 in that example, as I need to be able to use this to create a visual. 

I tried to pack your query into a sumx, but I cannot get it working.  

Do you have an idea how to do that?

PREVIEW

[rickrosa]

Hi @acg , any luck with the last part of your problem? I am trying to solve the exact same scenario as you were.