View Full Version : DAX - Parent Category Total

2017-03-15, 10:04 PM
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.


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

2017-07-24, 02:36 AM
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


2017-08-11, 10:05 PM
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]))


www.powerpivotpro.com (http://www.powerpivotpro.com)