Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Calculate Logic - Gross Margin Logic



Can someone help me with this Gross Margin Logic. I can's seem to figure it out.


I want to get the Gross Margins of Sales account 4130, but the cost is on a different row. There is a Offset Account Number 2. Can I get a logic that calls Calculate or a Sum of Cost Column in the row above with the offset Acct Number.


GM $ =  Calculate([Cost $],


if Acct_Number 2 = Account Number with my data Below.


Thanks for any Help!


Gross MArgins.png






Frequent Visitor

Hi @amitchandak 


I am so close to getting the correct result I want.


Can I just put a filter on this to get the correct Associated Date @ Account #2 with the Cost Source in this Calculated Column?


Here is my Calculated Column. It has the Cost Source in the right rows, but just I need the correct Cost Source Data associated with that Account #2 and Date.


Thanks for the Help!



New column =
                        MonthlyFinancials[dmf_AccountUid] = earlier(MonthlyFinancials[da_offset_id]) ),
GM Saved.png


Super User
Super User

@TBoat , You need to have one account table with a single name, may max of name.


Join this table with both account 1 and account2, If they are in the same table one join will inactive


Then you can create measure like , Assume account 1 is active join


calculate(Sum(Table[Cost]), isblank([Account 2]))


calculate(Sum(Table[Sales]), userelationship(Account[Account[, Table[Account2]), not(isblank([Account 2])) )



Else you have use earlier to move data across rows


Power BI DAX- Earlier, I should have known Earlier:


New column = Sumx(filter(Table, Table[Account] = earlier(Table[Account2]) ), Table[Cost])


Then you have ignore few  rows


!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Thanks for getting back to me @amitchandak I tried these solutions.


What is would the measure be if I wanted to get the Cost $ amount on the associated Sales Line?


  • I created a seperate Dim Table with relationship to Account to Fact Table Account , then Inactive Relationship from Dim Table Account  to Fact Table Account 2.


Cost Offset $ CY = calculate(sum(Fact Table[SalesSource]),

                        USERELATIONSHIP(Dim Table[Account],Fact Table[Account 2]),
                                    not(isblank(Fact Table[Account 2])))


This returns the Sales Number still. I want the Cost number, how do I get that in place of this? It is on a different Row. Shouldnt I sum costs? But then it doesnt have the reference of the Sales number. Not sure what I am missing.






Frequent Visitor

Still not sure how to move the data row down with Userelationship or Treatas. Or if this is possible.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors