Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shakeelkhan
Frequent Visitor

Need help: Matrix table

 

Dear,

Style no.ATTRIBUTEStore 1Store 2Store 3Store 4Store 5Store 6
NU529DGRY134820
NU529NAV250394
NU529RED3128542
P209MAR527603
P209BLK123361

 

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.

1 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.
stores.jpgsales.jpgon hand.jpgitem.jpgdata.jpg

View solution in original post

4 REPLIES 4
shakeelkhan
Frequent Visitor

Ashish_Mathur
Super User
Super User

Hi,

Clearly show the result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-zhouwen-msft
Community Support
Community Support

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

vzhouwenmsft_0-1714527823215.png

The Table data is shown below:

vzhouwenmsft_1-1714527924728.png

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] )
)

 

vzhouwenmsft_2-1714528099042.png

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

vzhouwenmsft_4-1714528717374.png

vzhouwenmsft_5-1714528732906.png

 

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.

 

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.
stores.jpgsales.jpgon hand.jpgitem.jpgdata.jpg

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.