Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |