Summarize from Multiple tables

[jordanxie]

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.

[jordanxie]

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?

[jordanxie]

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.

[v-rzhou-msft]

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.

[amitchandak]

@jordanxie , Not very clear. You can use what if parameter

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.