Dynamic Basket Case Analysis

[derromma]

Hello all, I am new to PowerBI and need your help. I have simplified it below:
I have a table 'Transactions' with the following details: 'Order Number', 'Product Number', 'Sales', which might look like this:
1 A 10€
1 B 15€
1 C 7,5€
2 A 10€
2 C 7,5€
3 B 15€
3 C 7,5€
...

I now want a page, where I can select a Product in a slicer and get a table visual, which shows all Products, that were bought in the orders, which contain the product filtered to and the corresponding sales of the individual products, which might look like this:

Slicer: Product A
Table Visual:
A 20€
B 15€
C 15€
...

So basically, I want the product slicer to filter for a list of orders, which contain the product, but the table visual to only reference to this list of orders and show the included products. This should be working dynamically, so it adjusts everytime I choose a different product in the slicer.

How do I achieve this? Many thanks for your help.

[_AAndrade · Accepted solution]

Hi @derromma,

Take a look at the new pbix file and see if it's what you're looking for.

[derromma]

Dear @_AAndrade , another question came up on my side on this topic:
I have a table visual which displays Sales on total OrderNumber Level (on orders that contain product X) which is also linked to a ProductNumber Slicer. It would be very helpful, if I could click on an order number from that table and have the bottom table (the one on Product Level) show only the products and corresponding valus that were in that specific order.
The current Measure "NewSum Basket" is not affected by filtering for a single Order in the table. This kind of Filtering worked however in the first solution you provided. Is there any way to make this work with the current measure "NewSum Basket"?

SumSales_Order =

CALCULATE (

    SUM ( 'T_Transactions'[Sales] ), ALL( 'T_Transactions' ), VALUES('T_Transactions'[OrderNumber] ))

NewSum Basket =

VAR _Table =

CALCULATETABLE(

    VALUES(T_Transactions[OrderNumber]),

    ALL(T_Transactions),

    T_ProdNames[ProductNumber] = SELECTEDVALUE(DiscProdTable[ProductNumber])

)

VAR _Sum =

    CALCULATE(

        [SumSales],

        T_Transactions[OrderNumber] IN _Table

    )

RETURN

    _Sum

[derromma]

Thank you!!

[_AAndrade]

Hi @derromma,

I'm attaching my solution in pbxi file.
The final output is this:

[derromma]

Many thanks for your swift help, really appreciated! It works a treat. I do have a follow up question though: If I now wanted to get the product name into the table visual, from a second table "masterdata" (which is linked to my 'Transactions' table via the "product number", how would this work?
If currently I add the 'product_name' to my table visual, it starts filtering to only the selected product of the slicer.