Calculate Logic - Gross Margin Logic

[TBoat]

Hi,

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!

[TBoat]

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 =

                maxx(filter(MonthlyFinancials,

                        MonthlyFinancials[dmf_AccountUid] = earlier(MonthlyFinancials[da_offset_id]) ),

                                    MonthlyFinancials[CostsSource])

[amitchandak]

@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])) )

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Else you have use earlier to move data across rows

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

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

Then you have ignore few  rows

[TBoat]

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.

Measure

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.

Thanks!

[TBoat]

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