DAX - Parent Category Total

[DickyMoo]

Hello All,

I am looking for a formula to calculate the total of the current Category, shown against each Sub-Category, as below:

Category Sub-Category Amount Category Total
Income Charitable Income 100 400
Income Other Income 300 400
Expenditure Staff Costs 200 700
Expenditure Other Costs 500 700


The problem is that I am using a Power BI chart which does not include Category,so I can't use the normal CALCULATE( [SUM], ALL( Account[Sub-Category]) ), that just gives me 1,100 for all rows.

I have been scratching my head for a while over this, does anyone know what approach I could try?

Thanks
Rich

 

[citizenbh]

Add column:

=CALCULATE(SUM([Amount Category]);FILTER('Table1';[Category]=EARLIER('Table1'[Category])))

 

[DickyMoo]

Thanks, that works as a column.

I should have specified that I am looking for a measure to use directly in the chart.

 

[Ozeroth]

Hi Rich,

You may well have figured this out in the mean time.
But I would use a measure like this:

= 
CALCULATE(
    SUM ( Account[Amount] ),
    ALL ( Account[Sub-Category] ),
    VALUES ( Account[Category] )
)

This is assuming your Sub-Category and Category columns are all in the same table.

Owen

 

[DickyMoo]

Thank you Thank you Thank you!!!

DAX is such a strange language, deceptively simple but you really have to know what you're doing.

Rich