How to Create Measures to Calculate Totals by ID, and by Types in Another Related Table:

[ylin88_waters]

Hi

I have two sales data tables linked by PartNumber:

Table 1 -

ID	PartNumber	SalesUnit
111	1011	10
111	2011	20
111	2012	15
222	1011	17
222	2011	8
222	2012	14
333	1011	12
333	2012	18

Table 2 -

PartNumber	ProdType	Type
1011	A	A
2011	B1	B
2012	B2	B

I'm trying to create following measures to calculate the sum of units by ID, and also by types in the table2:

1. Total Units: sum of all units by ID
2. Units_by_Type: sum of units by Type by ID
2. Units_by_ProdType: Sum of units by ProdType by ID

When I put them in a table visual on PBI dashboard, I should get following:

ID	ProdType	Type	SalesUnits	Units_by_ProdType	Units_by_Type	Total_Units
111	A	A	10	10	10	45
111	B1	B	20	20	35	45
111	B2	B	15	15	35	45
222	A	A	17	17	17	39
222	B1	B	8	8	22	39
222	B2	B	14	14	22	39
333	A	A	12	12	12	30
333	B2	B	18	18	18	30

Because the types are in another table,  that makes Dax code more complicated and I was not able to get it right. Any help is really appreciated.

[v-zhangti · Accepted solution]

Hi, @ylin88_waters 

You can try the following methods.

Column:

ProdType = RELATED('Table 2'[ProdType])

Type = RELATED('Table 2'[Type])

Measure:

Units_by_ProdType = CALCULATE(SUM('Table 1'[SalesUnit]),ALLEXCEPT('Table 1','Table 1'[ID],'Table 1'[ProdType]))

Units_by_Type = CALCULATE(SUM('Table 1'[SalesUnit]),ALLEXCEPT('Table 1','Table 1'[Type],'Table 1'[ID]))

Total_Units = CALCULATE(SUM('Table 1'[SalesUnit]),ALLEXCEPT('Table 1','Table 1'[ID]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ylin88_waters]

Yes, This works. Thanks for your help!!

YL