Forum Discussion
Advocate IVHide matrix subtotals for column
Hello,
I have a matrix with transactions in currency and in £ value.
e.g.
| Ref | Customer | Currency | Amount | £ Amount |
| T1 | C1 | USD | 100 | 75 |
| T2 | C1 | GBP | 200 | 200 |
| T3 | C2 | USD | 200 | 150 |
| T4 | C2 | USD | 100 | 75 |
I want to have a matrix with rows of customer and currency and Ref; and values of Amount and £ Amount
When the matrix is shown at customer level I want to see subtotals for £Amount,
but naturally it doesn't make sense to subtotal currency values across currencies unless the matrix is shown by currency.
Is there a way to selectively hide a column subtotal?
I think I've found my own answer - I'm going to post it here just in case it helps anybody else.
It's not possible to hide totals for a column, however it is possible to make them transparent! PowerBI supports alpha channel in colours.
It means they'll still be exported/cut&pasteable, but this suits my needs.
However ... the colour selector doesn't give you an option for transparency, so we have to do a little trick.
Create a colour table with a single RGBA transparent colour (#00000000) in it using the "Enter Data" table creation in Power Query.
Now in the matrix you can use this colour in the visual's "Cell Elements" settings - turn on the font colour
And then in the forumula set the colour for totals to the first value of the colour transparent from the data table.
That works - it's a bit of a hack, but I'm getting used to having to think outside the box with PowerBI...
I can even use a measure to hide totals if there are multiple currencies in the grouping level:
HideCCYColour = iF(HASONEVALUE(Sales[Currency]),blank(),"#00000000")
6 Replies
- Idrissshatila
Super User
Hello andyclap ,
yes sure, in the row subtotals, set the per row level "on", and then select the column you want and then set show subtotals to off.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
- andyclap
That doesn't seem to give me options for value columns - it turns off the total rows at certain levels.
As per the question, I want values to be shown but not totalled for the amount value. But the total line should be shown and the totals for the other values (£ Amount) shown.
- andyclap
For example with my output data when the matrix is fully expanded I would like to see
Customer Currency Amount £ Amount C1 USD 100 75 Total 75 GBP 200 200 Total 200 Total 275 C2 USD 200 150 100 75 Total 225 Total 225 Total 500 Or ideally to somehow show the total Amount total at the currency group level only (but if not possible, that's OK).
Customer Currency Amount £ Amount C1 USD 100 75 Total 100 75 C1 GBP 200 200 Total 200 200 Total 275 C2 USD 200 150 100 75 Total 300 225 Total 225 Total 500 Hope this clarifies
- andyclap
I think I've found my own answer - I'm going to post it here just in case it helps anybody else.
It's not possible to hide totals for a column, however it is possible to make them transparent! PowerBI supports alpha channel in colours.
It means they'll still be exported/cut&pasteable, but this suits my needs.
However ... the colour selector doesn't give you an option for transparency, so we have to do a little trick.
Create a colour table with a single RGBA transparent colour (#00000000) in it using the "Enter Data" table creation in Power Query.
Now in the matrix you can use this colour in the visual's "Cell Elements" settings - turn on the font colour
And then in the forumula set the colour for totals to the first value of the colour transparent from the data table.
That works - it's a bit of a hack, but I'm getting used to having to think outside the box with PowerBI...
I can even use a measure to hide totals if there are multiple currencies in the grouping level:
HideCCYColour = iF(HASONEVALUE(Sales[Currency]),blank(),"#00000000")
