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
TBoat
Frequent Visitor

Calculate Logic - Gross Margin Logic

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!

 

Gross MArgins.png

 

 

 

 

 

4 REPLIES 4
TBoat
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 =
                maxx(filter(MonthlyFinancials,
                        MonthlyFinancials[dmf_AccountUid] = earlier(MonthlyFinancials[da_offset_id]) ),
                                    MonthlyFinancials[CostsSource])
 
GM Saved.png

 

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

 

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

 

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
Frequent Visitor

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

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.