Gross Profit when COGS not available

[sagadgreat]

So I'm hoping this is not a unique problem.  My company only pulls actual COG data every Wednesday due to Oracle limitations.  Therefore, we would use extended shipment cost in place of COGS for dates when the information is not available.  HIstorically, this meant you would only get margin information after COGS is updated, but we would like to have something that updates daily.  

In the below, I am using a formula to calculate "21 Prf Cost" which is IF(COGS = blank(), Extended Cost, COGS).  I then have a Gross Profit calculation that is 21 Prf Cost/Net Sales. 

The issue as you can see below is that while it works at the line levels, it does not apply to the total properly and therefore skews what should be closer to a 42% margin, making it a 72.89% total.  The highlighted shows that both COGS and the Prf Cost lines are the same, even though the Extended Cost figures are carried over to Prf Cost properly.    

How do I get BI to not only replace the value of COGS with Extend Cost, but also add it to the totals to do the proper calculation?  Thanks in advance, this community is awesome!! 

[parry2k · Accepted solution]

@sagadgreat change your measure

21 Prf Cost = 
SUMX ( VALUES ( Datetable[Date] ), 
IF([COGS]==BLANK(), [Extended Cost], [COGS])
)

I assumed all these are measures:

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡