Log In
Register
Home
December 16, 2021
Offline
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
Offline
Hi Marko,
If you have proper dates in your model, which you should, then you can use the time intelligence functions like SAMEPERIODLASTYEAR etc. to find the price for the previous year and calculate the difference.
Mynda
December 16, 2021
Offline
Hi Mynda,
thank you for quick reply.
I have tried to implement function that you suggested, but with no result. I am not much familiar with DAX syntax.
Kindly ask you to check attached file with sample of data.
Thank you for support.
BR,
Marko
July 16, 2010
Offline
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
December 16, 2021
Offline
Hi Mynda,
thanks a lot.
When I want to create bar chart, is it possible to hide the columns for 2021? I would like to show only difference.
Best regards,
Marko
July 16, 2010
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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)
