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.

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.