Sales Ecosystem - Share, Collaborate, Learn & Connect

We’re relaunching the @Sales Ecosystem this month. Everything will be public this month within the forum.

Tell us about:

  • What you’re working on?
  • Something interesting Power BI related to your industry/job role
  • Any relevant challenges you’ve solved recently

This Ecosystems thread is a great opportunity to collaborate with others in community dealing with similar things to you.

Let’s start some conversations!

Hi @Sales, @SalesEcosystem, @ankit, @GarryA, @ojones, @Paul, @Vishy, @Yatz86, @Heather, @craig.murphy, we aim to consistently improve the Enterprise DNA Ecosystems by upgrading a centralized location of talented Power BI users and analyst with others in similar industries or business functions through the forum. With this, we aim to help you find an avenue where you can collaborate and connect within the forum. Interested to hear what you’ve been up to lately with Power BI.

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