Forum Discussion
Matrix visualization: assigning (fixed) values in Total cell
You could use a measure for the second column and use the ISINSCOPE() function to determine what value to use. For example, I created the following 2 tables in PBI (also: please never post pictures of tables because now we have to recreate it for you):
Country Person Actual Sales Target Sales USA Person1 150 150 USA Person2 160 130 USA Person3 120 140 Canada Person1 200 250 Canada Person2 100 200 Canada Person3 150 150 Mexico Person1 100 120 Mexico Person2 100 130 Mexico Person3 100 140 Country Target Sales USA 350 Canada 540 Mexico 360 I then created the following measure, which looks at the current context and determines if it is evaluated in a Country context of Person context. If it is the first case, than it will lookup the corresponding sales target in the second table, otherwise it will sum the Sales[Target Sales] column:
TargetSalesMeasure = SWITCH( TRUE(), ISINSCOPE(Sales[Person]), SUM(Sales[Target Sales]), LOOKUPVALUE(CountryTargets[Target Sales], CountryTargets[Country], SELECTEDVALUE(Sales[Country])))This results in the following matrix:
Neat stuff huh? 🙂 Let me know if this answers your question!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
You could use a measure for the second column and use the ISINSCOPE() function to determine what value to use. For example, I created the following 2 tables in PBI (also: please never post pictures of tables because now we have to recreate it for you):
| Country | Person | Actual Sales | Target Sales |
| USA | Person1 | 150 | 150 |
| USA | Person2 | 160 | 130 |
| USA | Person3 | 120 | 140 |
| Canada | Person1 | 200 | 250 |
| Canada | Person2 | 100 | 200 |
| Canada | Person3 | 150 | 150 |
| Mexico | Person1 | 100 | 120 |
| Mexico | Person2 | 100 | 130 |
| Mexico | Person3 | 100 | 140 |
| Country | Target Sales | ||
| USA | 350 | ||
| Canada | 540 | ||
| Mexico | 360 |
I then created the following measure, which looks at the current context and determines if it is evaluated in a Country context of Person context. If it is the first case, than it will lookup the corresponding sales target in the second table, otherwise it will sum the Sales[Target Sales] column:
TargetSalesMeasure =
SWITCH( TRUE(),
ISINSCOPE(Sales[Person]), SUM(Sales[Target Sales]),
LOOKUPVALUE(CountryTargets[Target Sales], CountryTargets[Country], SELECTEDVALUE(Sales[Country])))
This results in the following matrix:
Neat stuff huh? 🙂 Let me know if this answers your question!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂