Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.