Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jwin2424
Resolver I
Resolver I

Same measure shows different results in different visuals.

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?

jwin2424_0-1671483068998.png

 

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%.

1 ACCEPTED SOLUTION
jwin2424
Resolver I
Resolver I

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)

View solution in original post

3 REPLIES 3
jwin2424
Resolver I
Resolver I

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)
FreemanZ
Super User
Super User

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

 

Hi @FreemanZ ,

jwin2424_1-1671558538906.png

* 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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.