Forum Discussion
Helper VHow to add a specific subtotal in a matrix
Hi
I have a matrix table which I'm trying to find a way to sub-total 4 out of 5 territories. What is displayed in the screenshot is a principal or company and my team needs to see Metro, Mid, PA and UNY as a grand total and NE needs to be by itself. Is there a way I can successfully achieve this through formatting the visual?
Thanks
Emma
emma313823 you should add new group column, better to add in PQ but with DAX you can do something like this:
New Group = IF ( Table[Column] IN { "Metro", "Mid", "PA", "UNY" }, "ITC South", "Other" )In the matrix visual, on rows, add this new group column and under it add territory column and that will do it.
3 Replies
- parry2k
Super User
emma313823 you should add new group column, better to add in PQ but with DAX you can do something like this:
New Group = IF ( Table[Column] IN { "Metro", "Mid", "PA", "UNY" }, "ITC South", "Other" )In the matrix visual, on rows, add this new group column and under it add territory column and that will do it.
emma313823I'm sorry could you clarify PQ please? I tried adding new group...see screenshot.
With the dialogue box opened, I renamed to ITC South, selected the Principal of ITT Cannon Connector, but am at a loss as to what to do next?
- parry2k
emma313823 I said add a new column, preference is to add in the Power Query (PQ) but I gave you the solution on how to add a column using DAX. You don't need to add a group, just add a new column, give it a name, I called "New Group", you can call it whatever, and then use the DAX expression I gave in my original reply.