I currently have a spreadsheet that tracks weekly sales by sales rep.
This allows me to create simple graphs showing cumulative and YTD sales, gross margin and gross margin %
The way it is structured in excel allows me to show these graphs in total and also by individual.
Graphs look like this.
I like this as you can see the prior year but also where the current YTD is tracking versus previous years.
I wanted to use Power BI to make the data more dynamic – easier to slice and dice by sales rep, or sales channel or franchise.
I have produced a very simple dashboard that is mostly an excel import with a couple of extra measures added. It is nothing special but I thought this would be a good start for my journey into Power BI – nothing too complex and I know what the right answers are from the previous method to sense check the results.
Although the formatting could be polished I have managed to get the YTD information to filter by rep in the dashboard as shown below
The YTD total is £4.661m on the power BI graph and matches that of the total.
If I filter the Power BI by REP it works too – the below is rep AB and matches his 2021 number – Success
BUT THE power BI graph does work for cumulative position. Week no across the bottom sales in the vertical axis but it DOES NOT change when I filter by REP – it stays the same and ignores the rep filter completely
The difference is that the cumulative calculation is a measure and not just from a table as the YTD figures is so my guess is that my formula in the measure is lacking but I cannot figure out how to fix it.
Welcome to the forums,
Congratulations on getting everything right actually! the only small detail that you missed is the ALL funciton,
It removes all filters on the sales table before applying the calculation ( so it removes the sales rep you’re trying to get into it as well) you should replace it with ALLSELECTED,
Hi @NeilR! Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
I have spotted another little difference with the formulas I have.
In the spreadsheet version, each week is included in the data. For example the graph below shows weeks with no sales and make a flat line. This allows you to see more clearly when there are weeks with no sales.
However, within the Power BI model the formula seems to ignore the weeks where there are no sales.
It seems the formula only includes data when the cumulative data changes but I really want the data to calculate and include the cumulative value for every week - even if the cumulative model has not changed…
I have spotted another little difference with the formulas I have.
In the spreadsheet version, each week is included in the data. For example the graph below shows weeks with no sales and make a flat line. This allows you to see more clearly when there are weeks with no sales.
However, within the Power BI model the formula seems to ignore the weeks where there are no sales.
It seems the formula only includes data when the cumulative data changes but I really want the data to calculate and include the cumulative value for every week - even if the cumulative model has not changed…
Power BI practice.pbix (351.9 KB)
