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
homboy27
Helper I
Helper I

Difference column in a matrix

ClientHoursRevenue
X12320000
Y10010000
 2310000
   

I created the above table in PowerBI. How do i create the 3rd column that subtracts client x from y?

1 ACCEPTED SOLUTION

@homboy27 the solution I proposed should work, doesn't matter number of records. Pbix is attached for your reference.

 

 



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.

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

Assuming Hours and Revenue are measures, try this measure

=[Revenue]-CALCULATE([Revenue],Data[Client]="Client X")

Hope this helps.


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

That didnt work. Also hours and revenues are not measures.

Hi,

Share the link from where i can download your PBI file.  Also show the expected result.


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

@homboy27 add two new measures as belowo, drop table visual with Client, Total Hours, Total Revenue and you will have your solution

 

Total Revenue = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Revenue] ) ,
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Revenue] ), Table3[Client] = "Y" )
)

Total Hours = 
IF(  ISINSCOPE( Table3[Client] ),
    SUM( Table3[Hours] ) ,
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "X" ) -
    CALCULATE( SUM( Table3[Hours] ), Table3[Client] = "Y" )
)


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.

That did not work either

@homboy27 you have to provide more information, just saying, it didn't work, doesn't help to understand what is not working. It is very hard to judge what you are seeing and getting, so provide more information if you need help.

 

here is how it looks at my end

 

image.png



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.

it says calculation error. column is part of composite key, but not all columns of the composite key are included in the expression  or its dependant expression

Sorry let me more clear. i am trying to subtract from same columns. For example i want to add up total hours for client x  and client y and i would like to subtract them. i also want to do the same for reveue.

 

ClientHoursRevenue
X112
X212
X3334
X434
Y534
Y6343
Y743
Y834

@homboy27 the solution I proposed should work, doesn't matter number of records. Pbix is attached for your reference.

 

 



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.

ClientHoursRevenue
X12320000
Y10010000
Expected Result2310000

 

please see above. i created a table with the first 3 rows. Expected result column is what i would like to create. It basically subtracts the value from columns 2 and 3.

homboy27
Helper I
Helper I

I created the first 2 columns and would like to create the 3rd column that subracts the first 2 columns

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.