Thanks for this Mynda, Actually the negative changes confused me a little bit, I will recap that tomorrow for undertanding it completely. As always, great video!🤓
I agree with you! There is a difference between calculating a % change when the data actually shows 0 vs. calculating % change from nothing/no data - which is what happens in the first year. I mean - what would happen before you start tracking and you try calculating a % change of (0 - 0)/0, which is 0/0 or the infamous “indeterminate”, where you could assign any value as a % to it, since it could be anything since nothing happened in the business yet. It is “nothing over nothing” - on one hand it should be 0, because you are divide 0 by a number. On another hand, it should be 100%, because the numerator is equal to the denominator, etc.
Thanks for sharing your views. I think when there was nothing in the period before, the user should read the absolute values rather than a percentage change. Even if you put 100%, it's not helpful without referencing the actual values, so better to direct the reader to view the actual value than rely on a percentage.
Hi Mynda, really informative video. Also would greatly appreciate a video dedicated to the ''show values as'' feature of pivot tables. Thanks for sharing all of these helpful tips.
This is very helpful when you have few variables like the months and sales. I have a more complex data set that I need to show both a high level view down to a micro level. In my data set I have a whole budget that gets broken down into many offices. The offices furth spread the budget among various projects. Each project has a unique naming convention to track the budget. On top of that each project has to submit a monthly spending plan we then compare to their actual spending. I need to take the micro detail from the projects and then keep rolling the over data up to higher levels.
Thanks for this, % v actual can be major for me on the % misrepresentation measures, especially when consider absolute, high % “margin” but with small absolute as argument is we do pay staff, suppliers and shareholder with % but with cash…..
You are kidding when you ask "I hope you find my tutorial useful", may be because its your sure statement every video. Let me tell you video is not just useful but will make non-finance people also understand why they get negative signs as % change. Awesome.
Cheers, David! I agree, with the dates grouped the percentages don't span years. When I need multi-year comparisons I add a column to my source data for the period and use this formula to extract the period: =TEXT([@Date],"yyyy-mm") and use this field in my PivotTable.
This works perfectly for me an I even shared it with other colleagues. My one question is that my % column and my Change column both show totals at the bottom. In your example the totals are blank. What did I miss?
Great to hear, Rose! Not sure why your PivotTable includes a total percentage change. I'm happy to take a look, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great to hear. Not sure what you mean by 'show these errors on a Pivot Chart'. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi. I just found your channel & it's amazing. Really help me! But I have a new problem here :( Is it possible to calculate month by month over the year? For example, it can be calculate feb vs jan, mar vs feb. But is it possible to compare jan 2023 vs dec 2022. Because at jan 2023 the column to showing the %difference. Thank you.
Glad you're enjoying my channel, Benedict! To perform the calculation you describe, you have to write a custom DAX measure, which means adding your data to the data model. Hard to explain here, but if you get stuck, you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Not sure what you mean by accumulated returns, but percentage change in PivotTables is covered here: www.myonlinetraininghub.com/excel-pivottable-percentage-change
@@MyOnlineTrainingHub thanks, yes this video I saw. My problem is when I use the formula product and than like to draw a Chart… I use it for Stock / Portfolio returns where I like to see the accumulated y returns by year and the full Periode in %.
Great, but how do you do this when your year doesn't go from Jan to Dec, or you have more then 1 year and want to see the month change for each month, also from Dec to Jan? I would like to see changed from month to month over multiple years, where Jan is calculated using the previous years Dec. I'm not from US and my company financial year goes from July to June :-)
When I first put the Month field into the PivotTable it recognises this field contains dates and it automatically groups them into a new field called 'Months'. I undo that auto grouping which removes the Months field.
Problem: When I am inserting a slicer I am loosing the conditional formated column and the labels are going back to numbers. How can I fix this please?
I have Excel on my mac and running Excel 16.57. my drop downs stop at % of Parent Row Total.. So i dont even have the option to select % Differance From??
In the conditional formatting dialog window she brings up at 2:45, you'll see that the icons for each of the three ranges (lower left) can be selected from a dropdown menu. Alternatively, you can switch the sign for the ranges.
In the conditional formatting rule dialog box there is a button called 'Reverse Icon Order'. This will apply red down triangles to positive values and green up triangles to negative values.
Doing this over multiple years.... makes each january not compare to the previous december values... has to do with the grouping? why does the attribute of "previous" not get applied automatically there ?
to compare consecutive Dec > Jan, you have to Group By Month _and_ Year when specifying the Grouping levels at 1:06. Excluding the Year causes the pivot table to ignore the year portion of dates in your data and therefore to sum/avg/... all the Jan values, Feb values, Mar values, and so on to Dec.
Hi Koen, Correct, with the dates grouped the percentages don't span years. When I need multi-year comparisons I add a column to my source data for the period and use this formula to extract the period: =TEXT([@Date],"yyyy-mm") and use this field in my PivotTable.
Did I miss something? I was expecting to see the solutions for the negative change problems, but then the video ended. Great explanation of the problems though.
I wasn't addressing negative change, rather change from zero. Some people think a change from zero should be represented as 100%, which is mathematically wrong. Having a negative change is possible and ok, but if you have questions around this, I'd be happy to help. I won't see further replies to this thread, so please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for this focus on percentage. The percentage seems to be a so simple tool but it's much more complex and powerful than we think.
Glad it was helpful!
Thanks for this Mynda, Actually the negative changes confused me a little bit, I will recap that tomorrow for undertanding it completely. As always, great video!🤓
Yeah, I get that, hopefully a second watch will clarify things.
Thank you for sharing this most useful and informative video! The percentage calculations are very clear and helpful.
Glad you think so!
I agree with you! There is a difference between calculating a % change when the data actually shows 0 vs. calculating % change from nothing/no data - which is what happens in the first year.
I mean - what would happen before you start tracking and you try calculating a % change of (0 - 0)/0, which is 0/0 or the infamous “indeterminate”, where you could assign any value as a % to it, since it could be anything since nothing happened in the business yet. It is “nothing over nothing” - on one hand it should be 0, because you are divide 0 by a number. On another hand, it should be 100%, because the numerator is equal to the denominator, etc.
Thanks for sharing your views. I think when there was nothing in the period before, the user should read the absolute values rather than a percentage change. Even if you put 100%, it's not helpful without referencing the actual values, so better to direct the reader to view the actual value than rely on a percentage.
Hi. Could you please demo difference from and % difference from using DAX
Hi Mynda, really informative video.
Also would greatly appreciate a video dedicated to the ''show values as'' feature of pivot tables. Thanks for sharing all of these helpful tips.
Glad you liked it. Here's a tutorial on Show Values As: www.myonlinetraininghub.com/excel-pivottable-show-values-as
@@MyOnlineTrainingHub Thanks!
This is very helpful when you have few variables like the months and sales. I have a more complex data set that I need to show both a high level view down to a micro level. In my data set I have a whole budget that gets broken down into many offices. The offices furth spread the budget among various projects. Each project has a unique naming convention to track the budget. On top of that each project has to submit a monthly spending plan we then compare to their actual spending. I need to take the micro detail from the projects and then keep rolling the over data up to higher levels.
I should think PivotTables can help you aggregate the data at the various levels you need so long as the source data is in the correct tabular layout.
Very useful info, great job Mynda!
Cheers, Chris!
Thanks. I did not know that there is "show value as". New knowledge everyday 😊
Happy to help!
Hi Mynda. Very useful tutorial and information! Thanks for sharing :)) Thumbs up!!
Thanks so much, Wayne!
That was really interesting, and thank you for providing the files , I shall be keeping them for future reference.
Glad it was helpful, William!
Super helpful and very timely for me! Thank you!
So great to hear, Billiam!
Great video Mynda! Thanks a lot.
Glad you liked it, Antonio!
Clearly explained & useful. nice job Thks for sharing
So nice of you, Joseph!
you ROCK! Thank you for this :-)
Glad it was helpful 🙏😊
Thanks for sharing this information!
Glad it was helpful!
Thank you for this very useful video!
Glad you enjoyed it!
Hi Mynda!Really Helpful Tutorial..Thank You :)
Great to hear, Darryl! Thanks :-)
Thanks for this, % v actual can be major for me on the % misrepresentation measures, especially when consider absolute, high % “margin” but with small absolute as argument is we do pay staff, suppliers and shareholder with % but with cash…..
Glad it was helpful :-)
Yay thank you very much :D my teacher asked me this :P
Glad I could help!
You are kidding when you ask "I hope you find my tutorial useful", may be because its your sure statement every video. Let me tell you video is not just useful but will make non-finance people also understand why they get negative signs as % change. Awesome.
Great to hear it will be helpful, Rajan!
Perfectly done!
Thanks so much, Luigi!
Wonderful! Thank you.
My pleasure, Ernesto!
Awesome video. thank you
Glad you liked it!
Good, very useful 👌
Great to hear, Reda!
Very useful as always Mynda. I was wondering if you can show us how to make month to month comparisson in Power BI.
I'll keep it in mind for future tutorials.
Happy Diwali !!!!🔥
You too, Akash!
Great video. I use this method a lot but when there is December data & January (the next month) it doesn’t work & gives a blank value sadly
Cheers, David! I agree, with the dates grouped the percentages don't span years. When I need multi-year comparisons I add a column to my source data for the period and use this formula to extract the period: =TEXT([@Date],"yyyy-mm") and use this field in my PivotTable.
@@MyOnlineTrainingHub ah yes that works - good call! Seems like a bug in the software that it can't process Dec to Jan!
@@MyOnlineTrainingHub So glad you posted this response and saved me from another obstacle
Brilliant!
Cheers, Vinod!
Thank you so much
You're most welcome, Ramz.
This works perfectly for me an I even shared it with other colleagues. My one question is that my % column and my Change column both show totals at the bottom. In your example the totals are blank. What did I miss?
Great to hear, Rose! Not sure why your PivotTable includes a total percentage change. I'm happy to take a look, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you, this helped. How do you show these errors on a pivot chart?
Great to hear. Not sure what you mean by 'show these errors on a Pivot Chart'. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Will do! I have a pivot table and want to create a bar chart that shows the % change from the pivot table on the bars.
Hi. I just found your channel & it's amazing. Really help me! But I have a new problem here :(
Is it possible to calculate month by month over the year? For example, it can be calculate feb vs jan, mar vs feb. But is it possible to compare jan 2023 vs dec 2022. Because at jan 2023 the column to showing the %difference. Thank you.
Glad you're enjoying my channel, Benedict! To perform the calculation you describe, you have to write a custom DAX measure, which means adding your data to the data model. Hard to explain here, but if you get stuck, you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, always very helpfull videos. Do you have a video with accumulated returns and % changes in PivotTable?
Not sure what you mean by accumulated returns, but percentage change in PivotTables is covered here: www.myonlinetraininghub.com/excel-pivottable-percentage-change
@@MyOnlineTrainingHub thanks, yes this video I saw. My problem is when I use the formula product and than like to draw a Chart… I use it for Stock / Portfolio returns where I like to see the accumulated y returns by year and the full Periode in %.
Great, but how do you do this when your year doesn't go from Jan to Dec, or you have more then 1 year and want to see the month change for each month, also from Dec to Jan? I would like to see changed from month to month over multiple years, where Jan is calculated using the previous years Dec. I'm not from US and my company financial year goes from July to June :-)
Hi Brent, you would have to use Power Pivot and write a custom DAX measure if your fiscal period is not in line with the calendar year.
Perfect! a question, when she clicked on the month cat on the field list there is another cat named months appeared - from where?
When I first put the Month field into the PivotTable it recognises this field contains dates and it automatically groups them into a new field called 'Months'. I undo that auto grouping which removes the Months field.
Nice
Cheers, Drew!
Problem: When I am inserting a slicer I am loosing the conditional formated column and the labels are going back to numbers. How can I fix this please?
Sounds odd. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I have Excel on my mac and running Excel 16.57. my drop downs stop at % of Parent Row Total.. So i dont even have the option to select % Differance From??
Mac Excel isn't exactly the same as the PC version, so I guess this is a limitation.
I have got a question for you : what if you work in procurement and you want to represent positive % with a red arrow? (such as cost increase)
In the conditional formatting dialog window she brings up at 2:45, you'll see that the icons for each of the three ranges (lower left) can be selected from a dropdown menu. Alternatively, you can switch the sign for the ranges.
@@DrAmgadSquires yes, I got that but you cannot have a red arrow going up. Only a red circle at best.
In the conditional formatting rule dialog box there is a button called 'Reverse Icon Order'. This will apply red down triangles to positive values and green up triangles to negative values.
Doing this over multiple years.... makes each january not compare to the previous december values... has to do with the grouping? why does the attribute of "previous" not get applied automatically there ?
to compare consecutive Dec > Jan, you have to Group By Month _and_ Year when specifying the Grouping levels at 1:06. Excluding the Year causes the pivot table to ignore the year portion of dates in your data and therefore to sum/avg/... all the Jan values, Feb values, Mar values, and so on to Dec.
@@DrAmgadSquires errrr no I have in fact grouped by month and year and still see the gap...
Hi Koen, Correct, with the dates grouped the percentages don't span years. When I need multi-year comparisons I add a column to my source data for the period and use this formula to extract the period: =TEXT([@Date],"yyyy-mm") and use this field in my PivotTable.
Bagus, terimakasih.
You're welcome :-)
Did I miss something? I was expecting to see the solutions for the negative change problems, but then the video ended. Great explanation of the problems though.
I wasn't addressing negative change, rather change from zero. Some people think a change from zero should be represented as 100%, which is mathematically wrong. Having a negative change is possible and ok, but if you have questions around this, I'd be happy to help. I won't see further replies to this thread, so please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub ok, thank you for the reply.