Forum Discussion

shakeelkhan's avatar
shakeelkhan
Frequent Visitor
2 years ago
Solved

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.

Need Help
Tutorial Requests
  • shakeelkhan's avatar
    shakeelkhan
    2 years ago

    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.


4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      shakeelkhan
      Frequent Visitor
      2 years ago

      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.