December 16, 2021
Hi,
I want to calculate item prices difference between two years.
Items are segmented by product group and quality group, divided by brands.
I have tried to add Calculated field in Pivot Table, but there is no option to choose price by year.
Thank you.
Best regards,
Marko
July 16, 2010
Hi Marko,
Thanks for sharing your file. There is an easier way. Simply right-click an of the values in the PivotTable > Show Values As > Difference From > Year and Previous.
Mynda
July 16, 2010
Hi Marko,
No, the PivotTable will always display the prior period and the chart will always display the data from the PivotTable. If you add a Slicer for the Brand you can then remove the legend and the chart will only display the brand selected in the Slicer. You can also set the series overlap to 100% and this will centre the bar to the InchSize label.
Hope that helps.
Mynda
December 16, 2021
Hi Mynda,
thanks for hints. It works great.
But I found out some other solution to work around the pivot table. I copied PT and past it as value, after that I created bar chart from this new table and edit source data to reference to my PT. I am not sure will it work in the future when my master table expand with new data.
I encountered on other difficulties now. How can I compare prices between two different brands for the same year?
Best regards,
Marko
July 16, 2010
If you've pasted the data as values then the chart won't reflect any updates to the PivotTable, so not ideal as you'd have to copy and paste as values each time you update the data.
You can also compare the brands using the Show Values As > Difference From setting.
Mynda
December 16, 2021
Hi Mynda,
You are 100% right. I have tested in the meanwhile and chart does not reflect any updates to the PivotTable.
So I had to drop out this solution, because it is not efficient.
I have to dive in more in DAX formulas and syntax. It seems to me to be much more powerful than the regular excel formulas.
What do you think is it better to have multiple tables (each for one brand) connected in one table or to have one big table with all data in it (that is my case at this moment)?
I am thinking to create tables for each brand separately and after that to connect them through the data model. Maybe is easier to calculate some measures in this way.
Best regards,
Marko
July 16, 2010
Hi Marko,
Definitely better to have one big table with all your data, than split it into separate tables for each brand. Having separate tables for each brand is not the way Excel or Power Pivot was designed to be used, so you will only come up against issues if you do that.
As I mentioned before, you can use Show Values As to calculate the difference between two different brands for the same year. See attached.
Mynda
1 Guest(s)