Forum Discussion

wshalex123's avatar
wshalex123
Helper III
3 years ago
Solved

Slicing with measures

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 visua...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi wshalex123 

     

    Please try:

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

     

    Best regards,

    Yadong Fang

v-yadongf-msft's avatar
v-yadongf-msft
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
wshalex123
Helper III
3 years ago

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!