Subtotal issues in a matrix

[Coppercrutch]

Hello all,

I've searched for similar issues but can't find anything quite the same. 

I've a matrix and for conditional formatting I've added icons based on rules. They work for for when I drill down into each section. I selected values only not subtotals. 

However when it's at the top view minimised it's showing subtotals for each row. Even though I've not selected that option ? Pics below for clarity. 

When I drill down the total icons disappear like they should. Strange ! 

Any help much appreciated. 

Cheers. 

 

 

 

[Coppercrutch]

Thanks very much. Worked a treat. For your total revenue part on the measure I couldn't work out why the one I needed wasn't popping up. Then realised it's a sum of a column so typed in sum [ and there it appears. Jackpot. 

Cheers 

[danextian · Accepted solution]

Hi @Coppercrutch 

How many row categories do you have? The only time the upper-level hierarchy doesn't show icons when the 'values only' option is selected is when you've drilled into the lowest level of the hierarchy. Until then, the upper-level hierarchies are considered values. You will need to write another measure to check whether the lowest level in the hierarchy is currently in scope and return a value if it is. In the screenshot below, ISINSCOPE will return true only at the geo level, thus it returns blank for the year and category row categories, resulting in no icons.

[Coppercrutch]

Ok thanks I will give that a go. Quick query - What exactly do you mean by the "geo" level ? 

8-10 row categories 

Cheers ! 

[danextian]

Geo is the lowest level in the hierarchy in my example.

[Coppercrutch]

Ah ok I get you. So for me it will be the individual resource. So this way I'm creating a measure based on this resource column.

And depending on the result I then use this measure rather than the resource column data to define the conditional formatting rules. 

Thus it won't show for the totals. 

I might get food at this stuff one day : 😁 

Will give that a go and let you know. Thanks for the help.