topic Re: Row level calculation, with variable elements, can't get total right. HELP! in DAX Commands and Tips

Row level calculation, with variable elements, can't get total right. HELP!

twilbour — Fri, 08 May 2020 18:27:36 GMT

I've been working in DAX for a while, but am still a neophite. I'm sure this is a no brainer for you guys, but I am STUCK!

I need a measure to calculate a Cost by Customer. The basic calculation is Revenue * Margin = Cost. The challenge is, each customer may have their own Margin, or not, and each division (customer is part of division) has a slightly different calculation.

The rules are:

Division A: Cost = Revenue

Division B: Cost = Revenue * Customer Specific Margin; if cust doesn't have own margin, use division margin

So table looks like this:

Customer	Division	Margin	Revenue	Cost (Desired outcome)
Joe	A	50	100	100
Bob	B	25	100	25
Sue	B		100	30

* Division B Margin = 30

I've been able to get the row level calculations done several different ways, but I haven't been able to get a total of all the cost (column total) to work.

I appreciate the help guys. I use this forum CONSTANTLY! HUGE HELP. I just couldn't figure out how to find this problem in one of the previous threads.

Re: Row level calculation, with variable elements, can't get total right. HELP!

Greg_Deckler — Fri, 08 May 2020 18:33:21 GMT

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Row level calculation, with variable elements, can't get total right. HELP!

twilbour — Fri, 08 May 2020 20:25:06 GMT

Thank You...The Summarize in your second post solved my problem! Y'all are awesome.