topic Re: Same measure shows different results in different visuals. in DAX Commands and Tips

Same measure shows different results in different visuals.

jwin2424 — Mon, 19 Dec 2022 20:54:13 GMT

Hello community! This is driving me crazy. Here is my measure:

Opp YoY = VAR _PriorYR = CALCULATE( SUM('Opportunity Product'[extendedamount] ), Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) -1 ) VAR _CurrentYR = sum('Opportunity Product'[extendedamount]) RETURN DIVIDE(_CurrentYR-_PriorYR,_PriorYR)

When I put this measure into a table, it yields the correct rate of change. When I put this SAME measure into a bar chart for the line, the results ADD 1. It should say 17%, but it says 117%. If I add -1 to the formula, then the table also minuses 1, and I don't want that. I want BOTH to say 17%. What am I missing?

EDIT: I figured I should also note that if I remove a 2021 from the bar chart, then the % is correct. It is only when I add another year to the bar chart that it becomes 117% instead of 17%.

Re: Same measure shows different results in different visuals.

FreemanZ — Tue, 20 Dec 2022 00:29:10 GMT

hi jwin2424

it seems you have month on the x axis, but it cant differentiate it is month of 2021 or 2022, so it sums.

To make YoY comparison, try to use DAX code to make the time/year shift, like DATEADD function.

check this post:

https://community.powerbi.com/t5/Desktop/DATEADD-function/td-p/2113441

Re: Same measure shows different results in different visuals.

jwin2424 — Tue, 20 Dec 2022 17:51:06 GMT

Hi FreemanZ ,

* Data updated, so percentages dont match the first post.

I took the measure and moved it from X to Y on the table, and the % is still correct for each month except for the grand total. The total is essentially taking 2022 and 2021 sum and dividing it by 2021. So you are correct that putting it on the X axis is causing this. The bar chart is giving me the Grand Total of the two years and not the total of the month.

I can't use DATEADD because I am on a fiscal calendar, and the YoY by month won't be accurate. I have already tried this, and the numbers aren't correct.

Any other suggestions?

Re: Same measure shows different results in different visuals.

jwin2424 — Wed, 28 Dec 2022 17:10:29 GMT

I found a solution. I used ChatGPT (OpenAI) and asked it the question, and it actually solved it. The error was that I was using the sum of the value and not the sum of the max value. Here is the correct code:

Opp YoY $ Measure = VAR _PriorYR = CALCULATE( SUM('Opportunity Product'[extendedamount] ), Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) -1 ) VAR _CurrentYR = CALCULATE( SUM('Opportunity Product'[extendedamount] ), Opportunity[createdon].[Year] = MAX (Opportunity[createdon].[Year]) ) RETURN DIVIDE(_CurrentYR-_PriorYR,_PriorYR)