DAX - Parent Category Total

Hello All,

I am looking for a measure to calculate the total of the current Category, shown against each SubCategory, as below:

Category SubCategory Amount CategoryTotal
Income CharitableIncome 100 400
Income OtherIncome 300 400
Expenditure StaffCosts 200 700
Expenditure OtherCosts 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[SubCategory]) ), 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? I have tried referring to the parent Category using Account[Category]=VALUES(Account[Category]) but can't find the right syntax.

Thanks
Rich


Note - this is a repost from here: https://www.mrexcel.com/forum/power-bi/995598-dax-parent-category-total.html

Try this:
Total = SUM(Account[Amount])
Total for Category = CALCULATE([Total],ALLEXCEPT(Account,Account[Category]))

If you have other fields on the chart you might need to add them in as well.

Also, I'd suggest you do not call your measure SUM, as might be confusing with the SUM function

Cheers

The following worked with your table in Power BI when I used a matrix visualization with subcategory on rows and the following measure in values:

Category Total:=CALCULATE([Total], ALL (Account ), VALUES ( Account[Category]))



Tom
www.powerpivotpro.com