Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Matrix Visualization Average Roll up - Ignore zeros/blanks

Team,   I'm looking for some assistance in how to have my matrix roll-up ignore zeros/blanks when providing a subtotal roll up. I've attached a picture of what I'm talking about. My data is calcula...
Need Help
Show and Tell
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Assume meausre [percentage] in my test file refers to the measure [.Utilization] in your file,

    Create measures

    count = COUNTX(FILTER(Sheet10,Sheet10[department]=MAX(Sheet10[department])&&[percentage]<>BLANK()),[percentage])

sum = SUMX(Sheet10,[percentage])

%_new = IF(ISINSCOPE(Sheet10[occupation]),[sum]/[count],[percentage])

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous 

Please check if my understanding is correct,

For the subtotal for D2 cell, the value should be

(99%+65%+100%)/3

instead of (99%+65%+100%)/4

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    v-juanli-msft That is correct.

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi Anonymous 

      Assume meausre [percentage] in my test file refers to the measure [.Utilization] in your file,

      Create measures

      count = COUNTX(FILTER(Sheet10,Sheet10[department]=MAX(Sheet10[department])&&[percentage]<>BLANK()),[percentage])

sum = SUMX(Sheet10,[percentage])

%_new = IF(ISINSCOPE(Sheet10[occupation]),[sum]/[count],[percentage])

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.