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
Community 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!

     

     

  • wshalex123's avatar
    wshalex123
    Helper III
    3 years ago

    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's avatar
      v-yadongf-msft
      Community Support
      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

      • wshalex123's avatar
        wshalex123
        Helper III
        3 years ago

        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)