Forum Discussion

jcolby's avatar
jcolby
Icon for Helper I rankHelper I
3 years ago

Ignore 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....
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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/n4TYhF2ARe8

 

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

jcolby's avatar
jcolby
Icon for Helper I rankHelper I
3 years ago

Hi Greg_Deckler,

Thanks for the reply

 

I'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 like

 

This 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