Slicing with measures

[wshalex123]

Hi,

i got a column, which includes all the overtime hours of employees every day.

I got 2 slicers, which give me the overtime of the selected employee in the selected timeframe. The result visualizes in a card visual:

In order to compare this to the average overtime hours of all employees, i made a second card visual. In this visual i disabled the interaction with the "Name" slicer, in order to get the average value of all employees (Here 85,10 in white). 

So far so good. But my problem is, that the "disabled interaction" only disables the slicing in the visual and not of the "calculated value" of the measure itself. So if i calculate with the the measure, i still got the scliced value instead of the 85,10!

In need this in order to make a conditional formating: "If value is above average value then green."

Maybe someone can help!

Thanks a lot

[v-yadongf-msft]

Hi @wshalex123 ，

This is my test table:

Please try following DAX to create an average measure, so you don't need to disabled interaction, the measure will always show the average of overtime hours.

Average of overtime = CALCULATE(AVERAGE('Table'[Overtime hours]),ALL('Table'))

Create another measure:

Conditional formatting = 
var overtime = SELECTEDVALUE('Table'[Overtime hours])
return
IF( overtime > [Average of overtime],"Green")

Conditional formatting for card visual:

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wshalex123]

OK at least got no error now:

DurchschnittMEHRA = CALCULATE((sum(_detaillierte_Tagesarbeitszeit[Kalk. GlidiingTimeBalanceNurPositiv]))/(DISTINCTCOUNT(_detaillierte_Tagesarbeitszeit[Employee])),ALL(_detaillierte_Tagesarbeitszeit))

But when i change the slicer, the value of the measure does still change

[v-yadongf-msft · Accepted solution]

Hi @wshalex123 

Please try:

DurchschnittMEHRA = CALCULATE((DIVIDE(sum(_detaillierte_Tagesarbeitszeit[Kalk. GlidiingTimeBalanceNurPositiv]),DISTINCTCOUNT(_detaillierte_Tagesarbeitszeit[Employee])),ALL(_detaillierte_Tagesarbeitszeit))

Best regards,

Yadong Fang

[wshalex123]

Hi @v-yadongf-msft,

it's still slicing... 

DurchschnittMEHRA = CALCULATE(DIVIDE(sum(_detaillierte_Tagesarbeitszeit[Kalk. GlidiingTimeBalanceNurPositiv]),DISTINCTCOUNT(_detaillierte_Tagesarbeitszeit[Employee])),ALL(_detaillierte_Tagesarbeitszeit[Employee]))

The value should be 85,58 (left white visual)

[v-yadongf-msft]

Hi @wshalex123 ,

Please note:

DurchschnittMEHRA = CALCULATE((DIVIDE(sum(_detaillierte_Tagesarbeitszeit[Kalk. GlidiingTimeBalanceNurPositiv]),DISTINCTCOUNT(_detaillierte_Tagesarbeitszeit[Employee])),ALL(_detaillierte_Tagesarbeitszeit))

If it still doesn't work, please share with me some data screenshots.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wshalex123]

Hi @v-yadongf-msft 

thanks for your response!

Sadly i can't use the DAX Average, because the denomineter of the average does not depend on the values of overtimes, but on the number of employees in another table.

So i tried to do it manually by dividing my two columns.

In this measure, i only can choose measure as a numerator/denomineter. But i need to divide the columns of two different tables. I always calculate in power query, still a little bit confused about measure calculating haha.

Maybe you can help me out on more time.

Thanks a lot!