Forum Discussion
Hide last level values from matrix table
Hi everyone,
I have a matrix table that l need to hide the value of last level.
Note: Im using measures.
Someone can help me with this?
This is how my data looks like:
| Continent | Country | State | Total |
| North America | USA | NewYork | 25 |
| North America | USA | Texas | 25 |
| North America | USA | Florida | 25 |
| North America | USA | Virginia | 25 |
| North America | Canada | British Columbia | 15 |
| Asia | India | Andhra Pradesh | 20 |
| Asia | India | Tamil Nadu | 20 |
| Asia | India | Karnataka | 20 |
| South America | Brasil | São Paulo | 30 |
My expecteds outputs: I want to show the output in Matrix table 3 levels (Continent, Country and State) sales. But I only want to see total in Continent and Country Level but if expand to State it should be blank as mentioned below.
-At Continent Level:
| Continent | Total |
| North America | 115 |
| Asia | 60 |
| South America | 30 |
-After expand to country level:
| Continent | Total |
| North America | 115 |
| USA | 100 |
| Canada | 15 |
| Asia | 60 |
| India | 60 |
| South America | 30 |
| Brasil | 30 |
-After expand to state level:
| Continent | Total |
| North America | 115 |
| USA | 100 |
| NewYork | |
| Texas | |
| Florida | |
| Virginia | |
| Canada | 15 |
| British Columbia | |
| Asia | 60 |
| India | 60 |
| Andhra Pradesh | |
| Tamil Nadu | |
| Karnataka | |
| South America | 30 |
| Brasil | 30 |
| São Paulo |
Thanks,
Lucas.
Hey salucas,
You can easily process this in a measure using the ISINSCOPE function. As long as the 'State' column is not in scope you will show the 'Total', otherwise not:
Measure = IF ( NOT ISINSCOPE ( 'Table'[State] ), SUM ( 'Table'[Total] ) )For 'State' make sure you show items with no data.
Result:
3 Replies
- Barthel
Solution Sage
Hey salucas,
You can easily process this in a measure using the ISINSCOPE function. As long as the 'State' column is not in scope you will show the 'Total', otherwise not:
Measure = IF ( NOT ISINSCOPE ( 'Table'[State] ), SUM ( 'Table'[Total] ) )For 'State' make sure you show items with no data.
Result:
Thanks! This works perfectly!
