topic SUBTOTAL in Matrix Visual as Row in DAX Commands and Tips

SUBTOTAL in Matrix Visual as Row

tim_001 — Tue, 25 Jun 2024 15:27:40 GMT

Hi,

I am facing an issue calculating a subtotal column. I tried to use CALCULATE SUM with FILTER but it does not really work.

Maybe somebody has a good idea? 🙂

Current Matrix Visual View:

	2024	2025	2026
Apples	4	10	23
Bananas	5	67	43
Cucumber	2	6	3
TOTAL	11	83	69

Trying to get this view with calculated row:

	2024	2025	2026
Apples	4	10	23
Bananas	5	67	43
SUBTOTAL FRUITS	9	77	66
Cucumber	2	6	3
SUBTOTAL VEGETABLES	2	6	3
TOTAL	11	83	69

Appreciate any ideas and feedback.

Kind Regards

Re: SUBTOTAL in Matrix Visual as Row

Greg_Deckler — Tue, 25 Jun 2024 16:27:29 GMT

tim_001 You could get something like this if you add a column to identify Fruits and Vegetables in your table. Otherwise, you are looking at a disconnected table and a single measure that identifies the row it is in and returns the appropriate calculation.

Re: SUBTOTAL in Matrix Visual as Row

tim_001 — Wed, 26 Jun 2024 06:35:10 GMT

Hi Greg_Deckler Thanks. So I would make another column that says if "Apple" then "Fruit" and so on. And then I can make a SUMIFS based on that column?

Re: SUBTOTAL in Matrix Visual as Row

tim_001 — Wed, 26 Jun 2024 06:56:59 GMT

Hi, I added a column and used this formula:

If(containstring(columnname, "X"), "desired", "none")

Now I just moved that in the first order of the matrix visual. So I have the Subtotal as one row and you can expend from there. I think this is the easiest way thanks.