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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.