Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.