Calculate a metric in detail table with rows filtered out

[fanx]

I am using a Header - Detail structure.

Table 1: Header

HeaderID	Sales
1	100
2	150
3	160

Table 2: Detail

HeaderID	Product	Qty	Price
1	A	2	30
1	B	1	40
2	C	1	150
3	A	2	30
3	D	2	50

I applied a slicer to filter product . It is easy for me to calculate metric in Table 1 with header including say Product A (HeaderID 1 and 3 are filtered).

However, I want to calculate the qty/sales of all items sold in the header which contains the product I selected in the slicer. I don't know how to do it.

Basically I want to see the below final extract if product A is selected in the slicer. Could anyone help?

	Qty
A	4
B	1
D	2

[Ashish_Mathur]

Hi,

If Product A is selected in the slicer, then why should A,B and D appear in the final output.

[parry2k]

@Ashish_Mathur it is explain in main post by @fanx. Did you read that?

[Ashish_Mathur]

Yes, i did.  He selects 1 and 3 from Table1 and then Product A from Table2.  That should still only show Product A.

[parry2k]

@Ashish_Mathur Ask is whatever product he selects, he want to show all the items of the headerid in which selected product exists. Anyhow I did already sent him the solution, feel free to go ahead if you have another option to solve it. 

[fanx]

Thank you both. It worked after removing the relationship. 

The business question I want to answer here is, for the order including item X (the ones I selected), which other items and what qty are sold together with it. That's why I want to calculate all the items in the order I filtered.

There is one more question on this, in my original model, I have a 'product' dimension table. It seems with this solution, I can only use product column from Detail table and not from product dimension table. when using product key from demension, it shows only the product A row (qty 4), no row for product B and D. but the total qty in the visual is correct (qty 7). Is there a way to walk around it?

[parry2k · Accepted solution]

@fanx you can try following steps:

- create table using dax for products to use in slicer

Product = VALUES( Detail[Product] )

- use Product from Product Table on slicer, and following measure to calculate qty

Total Qty = 
VAR __selectedHeaderId = CALCULATETABLE( VALUES( Detail[HeaderID] ), Detail[Product] IN VALUES( Products[Product] ) )
RETURN
CALCULATE( SUM( Detail[Qty] ), __selectedHeaderId )

- add table visual, put product and Total Qty mesure from detail table and you will have the result

[fanx]

Hi,

Thanks. I just tried. but it doesn't seem working. It still only shows the product A qty. Could you have another look at the sample file in the below link?

Power BI sample file

PS: Ideally, I would like to select the 'product' from 'product' table. because in my real model, I have more columns in my product table, e.g., category. and this could help me aggregate total qty to category level. But it there is no easy solution, I can go with the product level one.

Thanks again

[parry2k]

@fanx you have relationship between product and detail table which is not required, remove that relationship and it will work.