Forum Discussion

wshalex123's avatar
wshalex123
Icon for Helper III rankHelper 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
Icon for Helper III rankHelper 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
    Icon for Community Support rankCommunity 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
      Icon for Helper III rankHelper 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)
       

       

       

       

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

        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.