How to have Changing Values in a Matrix Table

[jameslat]

Hi,

Would appreciate if someone could jump in and make a test file for me because i cannot seem to find a solution anywhere regarding having a matrix table with a changing "values" section:

I have an excel sheet with 3 tabs:
1. Shows two stores and their monthly volume breakdown:

Store	Period	Vol (L)
no.1	Jan-24	211
no.1	Feb-24	250
no.1	Mar-24	315
no.1	Apr-24	354
no.1	Jun-24	679
no.1	Jul-24	927
no.1	Aug-24	645
no.1	Sep-24	392
no.1	Oct-24	677
no.1	Nov-24	403
no.1	Dec-24	78
no.1	Jan-25	101
no.1	Feb-25	249
no.2	Sep-24	441
no.2	Aug-24	496
no.2	Nov-24	568
no.2	Apr-24	726
no.2	Jan-25	738
no.2	Jul-24	919
no.2	Dec-24	980
no.2	May-24	1,053
no.2	Feb-25	1,218
no.2	Oct-24	1,298
no.2	Jan-24	1,375
no.2	Mar-24	1,582
no.2	Feb-24	1,785
no.2	Jun-24	2,365

2. The top 5 customers of those two stores and their overall volume contribution:

Store	Customer	Vol (L)
no.1	A	1,098
no.1	B	589
no.1	AL	435
no.1	M	370
no.1	C	333
no.2	AZ	2,413
no.2	D	1,798
no.2	BD	1,521
no.2	BF	1,208
no.2	E	1,171

3. The monthly breakdown of those top 5 customer's contribution:

Store	Customer	Period	Vol (L)
no.1	A	Jan-24	211.32
no.1	A	Apr-24	190.9
no.1	A	Feb-24	189.19
no.1	A	Jul-24	185.6
no.1	A	Sep-24	166.75
no.1	A	Mar-24	154.67
no.1	B	Jul-24	491.51
no.1	B	Jun-24	97.8
no.1	AL	Oct-24	115.47
no.1	AL	Aug-24	98.61
no.1	AL	Jun-24	81.83
no.1	AL	Feb-25	77.8
no.1	AL	Feb-24	61.07
no.1	M	Jun-24	136.8
no.1	M	Apr-24	117.06
no.1	M	Sep-24	116.37
no.1	C	Oct-24	263.75
no.1	C	Jan-25	69.14
no.2	AZ	Mar-24	583.5
no.2	AZ	Oct-24	466.27
no.2	AZ	Jan-24	298.09
no.2	AZ	Dec-24	247.69
no.2	AZ	Jul-24	187.96
no.2	AZ	Apr-24	156.06
no.2	AZ	May-24	105.2
no.2	AZ	Sep-24	90.15
no.2	AZ	Nov-24	86.94
no.2	AZ	Aug-24	69.38
no.2	AZ	Feb-24	62.7
no.2	AZ	Jun-24	30.5
no.2	AZ	Jan-25	28.43
no.2	D	Jun-24	1798
no.2	BD	Oct-24	427.79
no.2	BD	Jan-24	233.97
no.2	BD	Nov-24	161.97
no.2	BD	Dec-24	148.39
no.2	BD	Apr-24	128
no.2	BD	May-24	121.87
no.2	BD	Feb-25	101.72
no.2	BD	Jul-24	86.81
no.2	BD	Jun-24	61.17
no.2	BD	Mar-24	49.76
no.2	BF	Feb-25	305.73
no.2	BF	Nov-24	129.67
no.2	BF	Aug-24	125.67
no.2	BF	May-24	116.26
no.2	BF	Oct-24	116.05
no.2	BF	Jun-24	115.88
no.2	BF	Jan-25	109.03
no.2	BF	Feb-24	80.58
no.2	BF	Jan-24	61.67
no.2	BF	Apr-24	47.77
no.2	E	Feb-24	443.64
no.2	E	Jan-25	177.21
no.2	E	Jan-24	153.98
no.2	E	Oct-24	111.58
no.2	E	Sep-24	98.02
no.2	E	Feb-25	91.81
no.2	E	Apr-24	58.06
no.2	E	Dec-24	37.11

The initial store and overall volume view is fine:

But when i dropdown, the values dont change for top 5 customer overview / monthly breakdown:

I suspect i need to make a list of unique stores/customers and play around with the relationships but would really like someone to try it out for me as i cant seem to make heads or tails of it. Thanks

[mdaatifraza5556]

Hi @jameslat 

Here are the steps that can resolve your issue.

steps
---- Create one dim_store table
----- Create "Year" column in Top5CustBreackdown table

Below is the relationship between the table


and create a measure for your result

result =

SWITCH(TRUE(),

    ISINSCOPE(top5custBreackdown[Customer]), SUM(top5custBreackdown[Vol (L)]),

    ISINSCOPE(top5custBreackdown[year]), SUM(top5custBreackdown[Vol (L)]),

    SUM(stores[Vol (L)])

)

Here is the result 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

top 5 sales: = 
SWITCH (
    TRUE (),
    ISCROSSFILTERED ( customer[Customer] ), SUM ( top_5_customers[Vol (L)] ),
    SUM ( sales[Vol (L)] )
)