DIVIDE won't accept calculation as denominator

[edward_engler]

I have a developed a report relating to risk management that compares risks left over from 2023 and monitors progress as those risks are resolved. You can see that the gauge in the screenshot is showing the total percent accurately, which is simply dividing the count of backlog risks processed (1139) by the static total backlog count from end of last year (3451).

I made a measure to break this percent out by month as shown in the bar chart in the screenshot:

Backlog Variance % = 
DIVIDE(
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), PARALLELPERIOD(date_table[date_dt],-1,MONTH)) --Prev Month Risk Count
-
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])) --Current Risk Count
,
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), risk_table[snapshot_dt] = DATE(2023, 12, 31))) --Denominator calc

However, this measure ends up giving me a completely blank monthly chart:

BUT if I hardcode that measure denominator to 3451 like this:

Backlog Variance % = 
DIVIDE(
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), PARALLELPERIOD(date_table[date_dt],-1,MONTH)) --Prev Month Risk Count
-
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])) --Current Risk Count
,
3451) --hardcoded denominator

... then it produces the correct percentages!

The hardcoded denominator is not a long-term solution of course because I need this chart to respond properly to filters. Why is it that the DIVIDE function works with a static number in the denominator but not with a calc that should be giving the exact same value? (That calc I'm using in the denominator works perfectly fine for the card in the top left to give me the 3,451 total, btw.)

[edward_engler · Accepted solution]

Hello, after a lot of tinkering and trying a few things, I got the calculation to work properly. The denominator needed to have the date field from the CALENDAR table:

Backlog Variance % = 
DIVIDE(
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), PARALLELPERIOD(date_table[date_dt],-1,MONTH)) --Prev Month Risk Count
-
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])) --Current Risk Count
,
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), calendar_table[date] = DATE(2023, 12, 31))) --Denominator

However, I'm not sure WHY that fixed it. Can someone explain to me why I was getting a divide-by-zero error when using the calendar snapshot date from the risk table?

[Idrissshatila]

Hello @edward_engler ,

try putting the calc in a variable and reference it in the denominator.

[edward_engler]

Hi @Idrissshatila , thanks for your reply!
I did as suggested:

Backlog Variance % = 
VAR denominator = CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), risk_table[snapshot_dt] = DATE(2023, 12, 31))
RETURN
DIVIDE(
CALCULATE(DISTINCTCOUNT(risk_table[risk_id]), PARALLELPERIOD(calendar_table[date_dt],-1,MONTH)) --Prev Month Risk Count
-
CALCULATE(DISTINCTCOUNT('risk_table'[risk_id]), risk_table[snapshot_dt] = MAX(risk_table[snapshot_dt])) --Current Risk Count
,
denominator --variable
)

But unfortuantely I got the same result with a blank chart:

One thing that I noted is that if I put anything into the "If divide by zero" safety parameter, the chart always defaults to that value. In this case I entered "1" for that value:

For some reason, using my calc in the denominator, even as a variable, results in a "divide by zero" situation but I don't understand how.