Forum Discussion
Calculation order - rows versus columns
Hi Andrew-HLP,
Thank you for your update. It looks like the root cause is a combination of context transition issues and the DIVIDE function with multiple CALCULATE statements in your 'Gross Margin %' formula, which is causing a divide-by-zero error and preventing the SELECTEDVALUE function in your 'Var Adj' measure from working correctly.To resolve this, I recommend modifying the 'Gross Margin %' formula in your calculation group to directly reference the underlying data and simplify the context. Based on your findings, here's an updated formula that should work:
DIVIDE( CALCULATE( [Actual CM], KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] IN {"Income", "Cost of Sales"}) ) - CALCULATE( [Actual CM], KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales") ), CALCULATE( [Actual CM], KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Income") ), 0 )This formula calculates the gross margin by subtracting 'Cost of Sales' from 'Income' and dividing by 'Income', using the underlying data directly. This should eliminate the divide-by-zero error and allow the 'Var Adj' measure to correctly display the variance (e.g., 0.4% as seen in your test case).
If this resolves it, feel free to “Accept as solution” and give it a 'Kudos' to help others.
Thank you.
Hi Andrew-HLP,
Thank you for your update. It looks like the root cause is a combination of context transition issues and the DIVIDE function with multiple CALCULATE statements in your 'Gross Margin %' formula, which is causing a divide-by-zero error and preventing the SELECTEDVALUE function in your 'Var Adj' measure from working correctly.
To resolve this, I recommend modifying the 'Gross Margin %' formula in your calculation group to directly reference the underlying data and simplify the context. Based on your findings, here's an updated formula that should work:
DIVIDE(
CALCULATE(
[Actual CM],
KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] IN {"Income", "Cost of Sales"})
) - CALCULATE(
[Actual CM],
KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales")
),
CALCULATE(
[Actual CM],
KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Income")
),
0
)This formula calculates the gross margin by subtracting 'Cost of Sales' from 'Income' and dividing by 'Income', using the underlying data directly. This should eliminate the divide-by-zero error and allow the 'Var Adj' measure to correctly display the variance (e.g., 0.4% as seen in your test case).
If this resolves it, feel free to “Accept as solution” and give it a 'Kudos' to help others.
Thank you.
Thank you. Will try this as soon as I am back from vacation.
Hi Andrew-HLP,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.