Latest Enterprise DNA Initiatives

CRM Data Insights Event

We have out CRM data insight events happening tomorrow.

Perfect opportunity to learn more about a different type of data scenario with Power BI.

This isn’t going to be very advanced but a great refresher for all those involved in sales

We’ll be working through a full report development using Power BI

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.
For example:

  • 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 :slight_smile: 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 :slight_smile:
[Disclaimer: I am not sure this is the right place for this conversation.]


1 Like