Aggregation of unique subcategories by category

[ipeto]

Hi there,

I'm a total newbie in DAX and struggling with this dataset:

Categories	Subcategories	Value	Total category value [desired]
Cat A	Subcat 1	10	50
Cat A	Subcat 1	10	50
Cat A	Subcat 2	40	50
Cat B	Subcat 3	20	70
Cat B	Subcat 3	20	70
Cat A	Subcat 2	40	50
Cat B	Subcat 4	50	70

I have to create Total category value as a calculated column which contains the aggregated value of unique subcategories by category. For example 'Cat A' has two subcategories (Subcat 1 and 2) with multiple rows. 'Total category value' should be 10+40=50.

Thank you for your help!

[tamerj1 · Accepted solution]

Hi @ipeto

 

Total Category Value = 
CALCULATE (
    SUMX ( 
        SUMMARIZE ( 
            'Table',
            'Table'[Categories], 
            'Table'[Subcategories] 
        ),
        CALCULATE ( MAX ( 'Table'[Value] ) )
    ),
    ALLEXCEPT ( 'Table', 'Table'[Categories] ) 
)

[ipeto]

Thank you for the superfast answer!

I have found parts of the solution, but I was unable to put them together.