Forum Discussion
Show % difference between lines in a chart with tooltips
Hello,
I have the following data in a line Chart, it has all months of 2017 and just 9 months in 2018.
How can i display the sales difference in percentage between the years by month?
Thank you
Year Sales Month
| 2017 | 581.19 | 1 |
| 2017 | 545.75 | 2 |
| 2017 | 534.79 | 3 |
| 2017 | 493.26 | 4 |
| 2017 | 522.52 | 5 |
| 2017 | 529.76 | 6 |
| 2017 | 568.06 | 7 |
| 2017 | 516.28 | 8 |
| 2017 | 531.38 | 9 |
| 2017 | 525.72 | 10 |
| 2017 | 563.41 | 11 |
| 2017 | 593.38 | 12 |
| 2018 | 556.67 | 1 |
| 2018 | 517.12 | 2 |
| 2018 | 603.92 | 3 |
| 2018 | 580.71 | 4 |
| 2018 | 592.00 | 5 |
| 2018 | 592.22 | 6 |
| 2018 | 617.59 | 7 |
| 2018 | 623.25 | 8 |
| 2018 | 645.64 | 9 |
Hi Anonymous,
For another way, we could use dax to achieve that.
Measure =
CALCULATE ( SUM ( Table1[Sales] ) )
- CALCULATE ( SUM ( Table1[Sales] ), SAMEPERIODLASTYEAR ( 'Table1'[Year] ) )More details, you could refer to the attachment.
Best Regards,
Cherry
3 Replies
Hi Anonymous
Here are the steps I used to create to get that:
- open your query editor
- add a merged column to concatenate MONTH_YEAR
- sort on this column
- add two indexes, one from 0 and one for 1
- use the function Merge queries as follows
- expand the selection by keeping MONTH and SALES
- add a conditional column SAME MONTH to know if the next line of your current line refers to the same month
- add a custom column RESULT saying that IF the next line refers to the same month then substract
Then you get that:
Regards,
CR
Hi Anonymous,
For another way, we could use dax to achieve that.
Measure =
CALCULATE ( SUM ( Table1[Sales] ) )
- CALCULATE ( SUM ( Table1[Sales] ), SAMEPERIODLASTYEAR ( 'Table1'[Year] ) )More details, you could refer to the attachment.
Best Regards,
Cherry
It should be noted that nowdays we have Dynamic formating, solving this issue and working for charts as well.
