Matrix Visualization Average Roll up - Ignore zeros/blanks

[Anonymous]

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 calculating the percentage of time employees are working based on a standard amount of available time. The denominator is built by the day. I take the total number of working days in a month and multiply that time the total number hours in a day. For the numerator, I simply grab the hours employees work. Here are my measures. 

.sumtotalhours = CALCULATE(SUM('Actuals'[Actual Hours]))

.Sumtotalpossiblehours = CALCULATE(SUM('Users'[Day_capacity]))*SUM('Date'[Weekday])

.Utilization = ([.sumtotalhours] / [.Sumtotalpossiblehours])

There are times during the year where new employees have rolled onto or off projects. Thats why you'll find months where there is no data. I don't want my visualization roll up to consider those blanks/zeros. I want to ignore them. I just don't know how to do that. Thoughts?

Green is good. Red is bad. 

[v-juanli-msft]

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]

@v-juanli-msft That is correct.

[v-juanli-msft · Accepted solution]

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.