cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edwardroa
Helper I
Helper I

subtotal by multiple categories

Hi all,

 

Have searched through multiple threads but unable to find the solution. 

 

I'm needing a simple subtotal by 2 categories in the form of a calculated column. As an example, I need the below highlighted result please:

 

edwardroa_0-1628641251946.png

 

Thanks in advance, I'm sure it's quite easy but think my brain is fried trying to find the correct solution.

 

Kind regards,

Ed

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

=calculate(sum(data[liability]),filter(data,data[asset type]=earlier(data[asset type])&&data[year buckets]=earlier(data[year buckets])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

=calculate(sum(data[liability]),filter(data,data[asset type]=earlier(data[asset type])&&data[year buckets]=earlier(data[year buckets])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish,
When I get to the "EARLIER" function it doesn't allow me to select the Asset Type or Year Buckets columns?

edwardroa_1-1628642359045.png

 

 

Hi Ashish,

 

I get to the below part of the formula however when I start to type Asset Type, it doesn't bring up Asset Type as a column to select from?

edwardroa_0-1628643192100.png

 

My mistake Ashish,
I missed a bracket in your dax formula. It is working now, thank you very much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors