DAX SUM not working with filters

[Danny2020]

Hello, 

I have a large data Table 1 in the following form:

ID		Product		Type		Contribution Per Type		Measure 1		Measure 2
1		Shoes		A		100		20		30
1		Shoes		B		20		20		30
1		Shoes		C		30		20		30
2		Pants		A		40		10		50
2		Pants		C		50		10		50

I have a slider with filters for fields Type, subtype, from Table 2, showing the options in boxes (multiple selection):

Type   Subtype

A         

           AA

           AB

B         

           BA

           BC

C       

           CA

           CB

Table 1, and Table 2 are related by the Type field. The desired output table I would like to generate is:

Project Name   Total Contribution per type   Value 1      Value 2

Shoes                150                                        20                    30 

Pants                 90                                          10                    50

Total                 240                                          30                   80

The DAX measure I have for Value 1 is = CALCULATE (MAX(Table 1[Value 1]), ALLEXCEPT(Table 2, Table 2[Type], Table2[Subtype]))

The result for Value 1 is correct, row by row, and total, if all filters are selected. However, if I select in the filter  Type B in the slider, the values per row are correct, but the total remains = 30. If I select  B, the total for measure 1 should be 20.

How can I get a measure for Value 1 that shows the correct total when a filter is selected?

Thank you,

[v-jingzhang · Accepted solution]

Hi @Danny2020 

You can use the following two measures:

Value 1 Measure = MAX('Table 1'[Value 1])

Value 1 Total = SUMX(VALUES('Table 1'[Product]),[Value 1 Measure])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[amitchandak]

@Danny2020 , Try like

Sumx(Values(Table2[[Type]) ,CALCULATE (MAX(Table 1[Value 1])))

[Danny2020]

Hi @amitchandak,

Unfortunately the measure is overcounting the total for each project name, and also the general total.

Any ideas?

Thank you,