Sharing one of my challenges and solution regarding that.
Especially in FMCG sales, we need to look into both a shorter and longer period trend. Also, to understand the trends better, I feel looking into different perspectives.
- When I am designing any trade promotion, I am looking into a very recent history and forecast for immediate months. On the other hand, to forecast the next immediate months more accurately, I need to see the trend in the same period last year.
- While digging into a trend of 2-3 years, I felt to see not only “monthly total” figures rather seeing “moving total” and “moving average”. Also, what impactors were there in last year’s respective month, is good to have.
- I also aware that changing value types not possible from regular slicers.
As a solution, I firstly generated different charts, but it was becoming scattered and taking multiple sheets. But finally, I could have landed on a single page comprehensive chart like below:
Here in the same chart, I can see:
- Moving Average,
- Moving Total
- Monthly Total Primary Sales
- Monthly Total Secondary Sales
[Also, more parameters can be added like QTD, YTD as per individual needs. From the slicers above it can be sliced and diced as required.]
Also, I have a log of the last 2 year’s monthly activities which actually impacted sales trends on that month. Cool!
While doing this, I actually used the combination of:
- Generating a Secondary table to use selected value DAX
- Used measure branching and created the “Trend Selection” dax-:
[which is actually a set of Dax responds to selected value]
Trend Selection =
if(SELECTEDVALUE(‘Metric Table’[Trend Table],0)=“Monthly Sec”, [1. Sec],
if(SELECTEDVALUE(‘Metric Table’[Trend Table],0)=“3M Moving Total”, [3 Month Rolling total],
if(SELECTEDVALUE(‘Metric Table’[Trend Table],0)=“3M Moving Avg”, [3 month moving average],
if(SELECTEDVALUE(‘Metric Table’[Trend Table],0)=“Monthly PR”, [4. Primary],
if(SELECTEDVALUE(‘Metric Table’[Trend Table],0)=“YTD”, [1. Cumulative Sec], [1. Sec])))))
[I could have used variable here, but completed it as I didn’t feel it that much complex!]
And finally, I have organized the chart as below:
Hope you will love to see this. Also gratitude to our Mentor @sam.mckay who has shown these cool techniques of measure branching and playing with secondary tables.
I just brought those techniques to make my chart dynamic!
Looking forward to learning from all
[Disclaimer: I am not sure this is the right place for this conversation.]