cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Cumulative total minus another column

Hi All,

Its an enhancement to my earlier request. But not able to crack the logic. Please help.

I simply want to do subtraction of CumulativeTotal - AnotherColumn based on the product category filter. Here is the sample data with some expected outputs.

I was able to achieve the Green Column. But I am stuck on achieving the Yellow column.

Sample data:

 Product Category Amount_One Amount_Two Amount_Three Cumulative of Amount_Two Calculated Column 1 = IF(Table1[Category] = “Red”, [Amount_One]-[Amount_Two], [Amount_One]-[Amount_Three]] Calculated Column 2 = IF(Table1[Category] = “Red”, [Amount_One]-Cumulative of Amount_Two, [Amount_One]-[Amount_Three]] A Red 100 10 90 10 90 90 A Red 200 20 80 30 180 170 A Red 300 30 70 60 270 240 A Red 400 40 60 100 360 300 A Red 500 50 50 150 450 350 A Red 600 60 40 210 540 390 B Black 700 70 30 280 670 420 B Black 800 80 20 360 780 440 B Black 900 90 10 450 890 450 B Black 1000 100 0 550 1000 450 B Black 1100 110 -10 660 1110 440 B Black 1200 120 -20 780 1220 420
1 ACCEPTED SOLUTION
Super User

@vidyasagar159 I think this is what you are looking for, soluion is attached.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

2 REPLIES 2
Community Support

Hi @vidyasagar159 ,

I’m not sure whether the logic of calculated column 2 or the outputs in your sample data is what you want.

In that case I created two calculated columns as below:

Calculated Column 2 = IF('Table'[Category] = "Red", [Amount_One]-'Table'[Cumulative of Amount_Two], [Amount_One]-[Amount_Three])

Calculated Column 3 = [Amount_One]-'Table'[Cumulative of Amount_Two]

And the result would be shown as below:

If I misunderstood your meaning, please let me know.

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@vidyasagar159 I think this is what you are looking for, soluion is attached.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors