Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Green is good. Red is bad.
Solved! Go to 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.
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.
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |