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
bo_wang5
Helper II
Helper II

need generic measure that calculates YOY difference by different columns

I'm trying to write a measure that filters out future values when compairing YOY difference, and I was able to do this.  However, when I use that measure against customer, my total is correct by at the custome level the calcuation is not correct.  How do I modify my measure so that I the difference can be calculated across different columns and be correct?  Thank you.

https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing

 

bo_wang5_0-1628354307800.png

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.  Dragged Year and Month from the seperate columns of the Calendar Table rather than from the hierarchy.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

What is the problem?  In the Customer Table, the measure [diff YOY amount] returns the correct result in each row and in the Grand Total column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

you are correct I did a little more looking and the everything is calculating as expected.  I've since upgraded the file and now have a diffenent problem.

I wanted to apply a % increase to revenue last year and wanted to calculate a target based on last year's results.

I did this by creating a key in the 'calendar' table with a RELATED function to a table I called 'growth by month %' which contains the growth by month.  after I calcute this target, this target should sum up at the bottom but it does not.  Not sure why. link to the file is below.

 

https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing

 

bo_wang5_0-1628390478176.png

 

Hi,

You may download my PBI file from here.  Dragged Year and Month from the seperate columns of the Calendar Table rather than from the hierarchy.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

it worked, but I don't understand why, would you be willing to explain what you did?  I am very much a novice so I really want to understand this so I can apply it to other tasks.  Thanks.

Portrek
Resolver III
Resolver III

hi bo_wang5

you need to change the context..

 

try the measure below in the matrix with the red square.

 

measure = SUMX(VALUES(revenue[Customer]),revenue[revneue] - revenue[revenue ly])

 

Best regards.

I've updated my google drive file with your measure suggestion.

 

https://drive.google.com/file/d/11iIHWdMjnCOGJOmBBiIjFH2ljTKJ6vN3/view?usp=sharing

and the following is the output.  

 

when I slice the data to august (YTD), it is showing the correct calculation for revenue-revnue ly, in all rows except customer 3, the expected value there should be -31652.83.  

the goal is to create a measure that shows basically ytd difference reguardless of which column i'm operating on.  the measure I have now YOY diff simple is just subtracting revenue ly from revenue as a check. 

 

bo_wang5_1-1628359942002.png

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors