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

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.

3 Replies

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

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