Forum Discussion
Helper ICreate Subtotals in Tables for Categories and Include Them in a Matrix
Hello,
I have a question that's been puzzling me a bit. I need to create subtotals within a table for categories A, B, and then include those subtotals in the matrix or table. How can I do this?
Also, would it be the same if I work with measures instead of table values?
Thank you
If that's the view you are trying to achieve, the are work arounds. They're close but not there yet. Please see the attached sample pbix.
9 Replies
- SamWiseOwl
Super User
Hi Charcho
Put the category column in the rows above City:
This will group the rows by the category.
You can change the Row subtotals Position to Bottom to move the totals
- danextian
hi Charcho
Also, would it be the same if I work with measures instead of table values?
I am not sure what you mean but with matrix whatever you place to the value tile must be aggregated one way or another. They must always return a scalar value (min, max,, sum, average, count, first/last value of a text column).
As for the layout, please see below screenshot:
Note: dynamic subtotal text is not supported. You can change the text but the same text will be applied to all subtotal and total rows.
CharchoThank you SamWiseOwl. This helps, however I don't wan't to show the subtotal of categories C and D. How can I handle?
- SamWiseOwl
Hi Charcho
You can use a measure instead of your sum.
Hidden measure =
IF(
selectedvalue(table[category]) IN {"C","D"} && NOT(ISINSCOPE(table[City]))
,BLANK() --if on subtitle and either C or D then hide the data
,SUM(table[Value]) --Otherwise show the calculation
)- Charcho
Thank you SamWiseOwl, that’s a good idea! However, I don’t want the "C o D" category to be used as a subtotal. In this example, there are only two categories, but in reality, I have several more that I don’t want to display.
- danextian
If that's the view you are trying to achieve, the are work arounds. They're close but not there yet. Please see the attached sample pbix.
- Charcho
Hello, danextian, it's become more complicated because I have more categories to consider. I thought it would be easier which is why I started with some simple initial data. I'll show you what I need now. I've adjusted the measurements, but I'm not getting the result I want. If you could help me, I would learn how to handle more categories. The data is as follows:
