Dynamic sum in dax picking distinct values

Question

Below is sample data

Week Practice  Type capacity   Gen
1       BI      c       80      0
1       BI      c       80      1
1       BI      sc      160     1
1       BI      pc      240     0
1       BI      pc      240     3
1       BI      mc      1160    1
1       BI      mc      1160    4
1       BI      mc      1160    2
1       BI      ac      440     1
1       BI      d       40      0
1       BI      d       40      3

I have a pivot chart, that has 3 slicers namely PRactice, Type, and gen.

when I don't select any slicer, it should be a distinct sum(capacity) ie.,2120. Then when I click on type slicer say mc Sum(capacity) should be 1160 and click on only gen say 3 and clear other filters then sum(capacity) = 280.

There can be many practices and many weeks.

Need dax query to meet this requirement.

Answer 1

You need to define two DAX measures:

Support:=MAX(Table1[capacity])

and

DistinctSumOfCapacity:=SUMX(DISTINCT(Table1[Type]),[Support])

Now you can add DistinctSumOfCapacity to the value section of your pivot and you'll get the distinct sum.