PDA

View Full Version : DAX - Parent Category Total



DickyMoo
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.

Thanks
Rich


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

jafa1970
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

Cheers

tallan
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]))

7195

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