Forum Discussion
Helper IIgnore Blanks in Matrix Column Subtotals
Hi there,
I'm trying to have the matrix subtotal column ignore blank fields
I want the subtotals to be like this for the rows labeled 1,2,3
"Row 1" =1.2
"Row 2" = 3.6
"Row 3" = 2.8
etc
Instead of including the blank cells in the subtotal calculation.
Right now my measure for the value field on this maxtrix table is this
Tickets Resolved / WorkingDays =
CALCULATE(
DISTINCTCOUNT('Tickets Resolved'[Ticket_Number])
) / [Working Days]
I've seen a few threads and posts similar to this on here and stack but still unable to work out the solution in my case
How can this be achieved?
Any help is very greatly appreciated,
Thank you in advance!
2 Replies
- Greg_Deckler
Community Champion
jcolby First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hard to determine how to exactly solve the problem in your case. You'd have to do something like SUMMARIZE by month and your measure. Remove BLANKS via FILTER. SELECTCOLUMNS of your remaining month names. Do a COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Tickets Resolved',[MonthName] IN __MonthNames),"__Num",[Ticket Number])))
Something along those lines. Hassle.
jcolbyHi Greg_Deckler,
Thanks for the replyI've voted on your idea
These were some of the posts I came accross when initially searching but even with the info provided I'm a bit past my depth in trying to get his to work how I'd likeThis is the table setup
Measure:
Tickets Resolved / WorkingDays = CALCULATE( DISTINCTCOUNT('Tickets Resolved'[Ticket_Number]) ) / [Working Days]I cant quite make out the possible solution you proposed in the end of your post
Feel like I'm so close with that second youtube link you posted as well but still cant get anything to work.
Thanks again,
I'll keep trying/searching
If you could breakdown those possible steps into smaller chunks I would be very grateful
Cheers
