Multi table values to swap in Matrix Visual

[Anonymous]

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

[parry2k]

@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!

[Anonymous]

Thanks for your reply. Will check and let you know.

Regards,

MM