Need help: Matrix table

[shakeelkhan]

Dear,

Style no.	ATTRIBUTE	Store 1	Store 2	Store 3	Store 4	Store 5	Store 6
NU529	DGRY	1	3	4	8	2	0
NU529	NAV	2	5	0	3	9	4
NU529	RED	3	12	8	5	4	2
P209	MAR	5	2	7	6	0	3
P209	BLK	1	2	3	3	6	1

I want a matrix table like the above.
1. I need to filter one store, (for quantity =<5) it should only display those styles. BUT
2. The other stores should display all the quantity they have on those styles displayed.

I hope you have solution for this.
Thanks in advance for your kind cooperation.

[shakeelkhan]

@v-zhouwen-msft 

[Ashish_Mathur]

Hi,

Clearly show the result you are expecting.

[v-zhouwen-msft]

Hi @shakeelkhan ,

My understanding of 'all quantities' is that values of the same style are added together, regardless of attributes.

All the quantity for store1 = 6,All the quantity for store2 = 6

The Table data is shown below:

Let's assume that the store we are filtering for is 'Store 3'

Please follow these steps:

1. Use the following DAX expression to create a table

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Style no.],
    "Store 1", SUM ( 'Table'[Store 1] ),
    "Store 2", SUM ( 'Table'[Store 2 ] ),
    "Store 3", SUM ( 'Table'[Store 3] ),
    "Store 4", SUM ( 'Table'[Store 4] ),
    "Store 5", SUM ( 'Table'[Store 5] ),
    "Store 6", SUM ( 'Table'[Store 6] )
)

2.Use the following DAX expression to create a table

Table 3 =
ADDCOLUMNS (
    FILTER ( 'Table', 'Table'[Store 3] <= 5 ),
    "all_for_store1", LOOKUPVALUE ( 'Table 2'[Store 1], 'Table 2'[Style no.], 'Table'[Style no.] ),
    "all_for_store2", LOOKUPVALUE ( 'Table 2'[Store 2], 'Table 2'[Style no.], 'Table'[Style no.] )
)

3.Use the following DAX expression to create a measure

_Store 1 = IF(ISINSCOPE('Table 3'[ATTRIBUTE]),BLANK(),MAX('Table 3'[all_for_store1]))

4.Use the following DAX expression to create a measure

_Store 2 = IF(ISINSCOPE('Table 3'[ATTRIBUTE]),BLANK(),MAX('Table 3'[all_for_store2]))

5.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shakeelkhan · Accepted solution]

hi,
thanks for your support. I think i did not explained it clearly.


Here you find the screen shot of my data tables. 
REQUEST: Once again, my requirement is to filter any store
And the filtered store should show only the  Styles and attribute(color) 

Based on:   on hand qty to be <=5, and sales >= 5. 

The others stores should show their quantity on hand, for the styles 

Every time to change the stores in the measure will be very difficult, can we use slicers, filters to select.
Please help. If you need any other information plesae advice.