Forum Discussion
Multi table values to swap in Matrix Visual
Hello,
Have 3 tables (Regional, Location and Global) with expected sales target.
In Matrix Visual Row need to Swap Target if my Main table Category, Location and Region slicers selection.
Eg:
1. Items selected in Category Slicer but no items selected in Location and Region slicer
THEN Matrix Visual should show Global table Expected Target
2. Items selected in Location Slicer but no items selected in Category and Region slicer
THEN Matrix Visual should show Location table Expected Target
3. Items selected in Region Slicer but no items selected in Global and Location slicer
THEN Matrix Visual should show Region table Expected Target
| Ceated Power Bi Matrix Visual with below | |
| Rows | Category, Target |
| Columns | Report Mnth |
| Values | measure with Sum -Total sales Values / Sum -Monthly Expected Values |
Sample main table
| Category | Total sales Values | Monthly Expected Values | Report Mnth |
| A | 80 | 110 | Jan-20 |
| B | 75 | 100 | Jan-20 |
| C | 88 | 120 | Jan-20 |
| D | 65 | 100 | Jan-20 |
| A | 80 | 110 | Feb-20 |
| B | 75 | 100 | Feb-20 |
| C | 88 | 120 | Feb-20 |
| D | 65 | 100 | Feb-20 |
| A | 80 | 110 | Mar-20 |
| B | 75 | 100 | Mar-20 |
| C | 88 | 120 | Mar-20 |
| D | 65 | 100 | Mar-20 |
| Table 1 | ||
| Region | Category | Expected Target |
| EMEA | A | >=85% |
| EMEA | B | >=90% |
| EMEA | C | >=75% |
| EMEA | D | >=95% |
| APAC | A | >=85% |
| APAC | B | >=90% |
| APAC | C | >=75% |
| APAC | D | >=95% |
| NA | A | >=85% |
| NA | B | >=90% |
| NA | C | >=75% |
| NA | D | >=95% |
| Table 2 | ||
| Location | Category | Expected Target |
| AA | A | >=80% |
| BB | B | >=85% |
| CC | C | >=77% |
| DD | D | >=90% |
| EE | A | >=80% |
| FF | B | >=85% |
| GG | C | >=77% |
| HH | D | >=90% |
| II | A | >=80% |
| JJ | B | >=85% |
| KK | C | >=77% |
| LL | D | >=90% |
| Table 3 | ||
| Global | Category | Expected Target |
| A | A | >=85% |
| B | B | >=90% |
| C | C | >=75% |
| D | D | >=95% |
| E | A | >=85% |
| F | B | >=90% |
| G | C | >=75% |
| H | D | >=95% |
| I | A | >=85% |
| J | B | >=90% |
| K | C | >=75% |
| L | D | >=95% |
Thanks In advance,
MM
2 Replies
Anonymous create a dynamic measure based on selectedvalue function from your slicer to check which value is selected and then create a measure from the respective table:
For example:
Dynamic Measure = VAR __selectedValueSlicer1 = SELECTEDVALUE ( Slicer1Table[Column] ) VAR __selectedValueSlicer2 = SELECTEDVALUE ( Slicer2Table[Column] ) SWITCH ( TRUE(), __selectedValueSlicer1 <> BLANK() && __selectedValueSlicer2 == BLANK(), SUM ( Table1[Value] ), __selectedValueSlicer1 = BLANK() && __selectedValueSlicer2 <> BLANK(), SUM ( Table2[Value] ) )you can build the conditions based on your business rules.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
