Forum Discussion

jordanxie's avatar
jordanxie
Helper I
3 years ago

Summarize from Multiple tables

Hi,

 

Trying to build a report that uses multiple parameter tables and would like to summarize all those parameter tables as a one table with the selected value for each filter selection.

 

Each row and column combination is a filter/slicer for users to select a number. For example, Product 1 and Next Week Default Volume; If user changes it to 6, then the table updates accordingly.

 

Need Help

4 Replies

  • jordanxie's avatar
    jordanxie
    Helper I
    3 years ago

    Yes, I am using What-if parameter for the dynamic filters (Next Week, Next Month, Next Quarter, Rest of Year) for each Product. 

    The filter are independent of each other and I would like to summarize all the values in the filter into one table like example below.

     

    Hope it makes more sense on what I am trying to achieve.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi jordanxie ,

       

      I think you need to create 8 generate tables by if parameter for your 8 slicers.

      Then create four measures as below.

      Next Week = 
VAR _SELECT1 =
    SELECTEDVALUE ( 'Product 1 Next Week'[Product 1 Next Week] )
VAR _SELECT2 =
    SELECTEDVALUE ( 'Product 2 Next Week'[Product 2 Next Week] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Product'[Product] ),
        "Product 1", _SELECT1,
        "Product 2", _SELECT2
    )
      Next Month = 
VAR _SELECT1 =
    SELECTEDVALUE ( 'Product 1 Next Month'[Product 1 Next Month])
VAR _SELECT2 =
    SELECTEDVALUE ( 'Product 2 Next Month'[Product 2 Next Month] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Product'[Product] ),
        "Product 1", _SELECT1,
        "Product 2", _SELECT2
    )

      [Next Quarter] and [Rest of Year] are the same format as above.

       

      Best Regards,
      Rico Zhou

       

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

  • jordanxie's avatar
    jordanxie
    Helper I
    3 years ago

    Couple follow up questions:

    1. Does that mean I need to create 10 VAR if there are 10 products?
    2. Instead of generating 8 tables by if parameter, can I create only 4 or less tables with all the products in them like below which ignores filters? If the option works, how about adding the 'Next Month', 'Next Quarter' ,etc. columns in the same table?

    Can you also send me the pbix file with your example?