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

 

 

 

 

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

6 Replies

  • 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