Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ignoring axis context for a measure in chart

I´m trying to use ALLEXCEPT to delete the context for the axis in a chart but not getting there.

Given this data:

 

I´d like to add a line that shows the average of value across all categories.

I thought that was the use for ALLEXCEPT, the average measure i´m using is:

Average = CALCULATE(SUM(Table1[Value]) / DISTINCTCOUNT(Table1[Category]); ALLEXCEPT(Table1; Table1[Category]))

What am i missing?

 

EDIT: I should add, there are other fields filtering this visual, so I can´t use ALL, i.e., something like 

Average = SUMX(ALL(Table1);Table1[Value]) / CALCULATE(DISTINCTCOUNT(Table1[Category]); ALL(Table1))

will not work. I want every other filter to be respected except for the X axis.

  • Hi Anonymous ,

     

    To use the formula as below.

     

    Average = 
    CALCULATE (
        SUM ( Table1[Value] ) / DISTINCTCOUNT ( Table1[Category] ),
        ALLSELECTED ( Table1 )
    )
    

     

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    To use the formula as below.

     

    Average = 
    CALCULATE (
        SUM ( Table1[Value] ) / DISTINCTCOUNT ( Table1[Category] ),
        ALLSELECTED ( Table1 )
    )
    

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Yes, that seems to work great v-frfei-msft , thanks a lot!

       

      Now it seems clear 

      ALLSELECTED ( Table1 )

      is the key :smileytongue: