# DAX - Parent Category Total



## DickyMoo (Mar 13, 2017)

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 (Mar 13, 2017)

Add column:

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


----------



## DickyMoo (Mar 13, 2017)

Thanks, that works as a column. 

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


----------



## Ozeroth (Mar 19, 2017)

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 (Mar 19, 2017)

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


----------

