Creating a League of Branches/Customers Using Power BI (DAX)

Imagine your products are sold via a third-party agency which has several branches in a country. In order to motivate the agency to perform and meet sales targets, you design a League where branches are monitored and given scores on a weekly basis versus their target. ?

You get a daily sales report and now wonder how you can generate a visual that enables senior managers with great insights into this League.

Microsoft’s Power BI has DAX (Data Analysis Expression) language, which empowers data analysts to define & generate:

·???????? Data models

·???????? Tables

·???????? Columns

·???????? Measures

·???????? Quick measures

If you are in a rush to design such a League & provide your managers with such a visual or report, follow along. You simply need to replace some names. Please note that:

1.????? This is the solution that I came up with & worked for my organization. If you find any different solutions, do not hesitate to try them along with mine. They might work better for you!

2.????? You should first create a data model before going for these codes & functions. If you do not have the model yet, I do not recommend reading this post as you will find yourself juggling between tables with no tangible results in hand.

You should have a sales table (often called FactSales) which has at least the fields below:

·???????? CID (Customer ID)

·???????? Branch (this can change depending on your target identity)

·???????? Sales Date (called GregDate in my tables)

·???????? Week Lag (week lag is number of weeks past from current week)

·???????? Week ID

·???????? Sales amount (in any unit you desire)

·???????? Target (it can be target volume, target amount such as in USD, EUR etc., target coverage, target penetration, you name it)

In my solution, I calculated a total quarterly target (for sales in Liters and Boxes as well as penetration, which is distinct count of customers in a quarterly period.

Now let’s begin by DAX codes:

Weekly Branch Ranking (this is the name of the table that appears in data module of Power BI) =

--in your sales table, you need to calculate the date for customer's first order. In order for this formula to work, your sales date should be in Date format, or else it will fail. I added first order date as a new column to the current sales table:

VAR Calc_FirstOrder =

ADDCOLUMNS(

? ? FactSales1,

? ? "First Order Date",

? ? MINX(

? ? ? ? FILTER(

? ? ? ? ? ? FactSales1,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[CID]

? ? ? ? ? ? ) = FactSales1[CID] &&

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? ) = FactSales1[branch]

? ? ? ? ),

? ? ? ? FactSales1[Gregdate]

? ? )

)?

--next, we need to create a groupby table to have a new table with only needed columns, getting rid of unwanted ones:?

VAR GroupedTBL=

GROUPBY(

? ? Calc_FirstOrder,

? ? FactSales1[CID],

? ? FactSales1[branch],

? ? FactSales1[Gregdate],

? ? [First Order Date],

? ? FactSales1[Greg YM],

? ? FactSales1[Week Lag],

? ? FactSales1[Week ID]

)

--now, I need to add a flag which helps me distinguish between records that are needed & other ones. it can also be counted to see actual penetration.

VAR Adding_Customer_FirstOrder_Flag =

ADDCOLUMNS(

? ? GroupedTBL,

? ? "First Order Flag",

? ? IF(

? ? ? ? [First Order Date] =

? ? ? ? FactSales1[Gregdate],

? ? ? ? 1,

? ? ? ? 0

? ? )

)

?

--now I can freely get rid of unwanted records:

VAR only_flagged_orders =

FILTER(

? ? Adding_Customer_FirstOrder_Flag,

? ? [First Order Flag] = 1

)

?

--finally, I can create a table which shows actual penetration of each branch per week

VAR Actual_Pen_weekly =

GROUPBY(

? ? only_flagged_orders,

? ? FactSales1[branch],

? ? FactSales1[Week ID],

? ? FactSales1[Week Lag],

? ? "Weekly Actual Pen",

? ? SUMX(

? ? ? ? CURRENTGROUP(),

? ? ? ? [First Order Flag]

? ? )

)

?

--I can also create a separate table where I can sum actual sold Liters & Boxes :

VAR Actual_Lit_Box =

GROUPBY(

? ? FactSales1,

? ? FactSales1[Week Lag],

? ? FactSales1[Week ID],

? ? FactSales1[branch],

? ? "Weekly Actual Lit",

? ? SUMX(

? ? ? ? CURRENTGROUP(),

? ? ? ? FactSales1[Net Sales (Liters)]

? ? ),

? ? "Weekly Actual Box",

? ? SUMX(

? ? ? ? CURRENTGROUP(),

? ? ? ? FactSales1[Net Sales (box)]

? ? )

)

?

--here, I add actual penetration to the table where I calculated actual sales of Liters & Boxes

VAR Adding_Actual_Weekly_Pen =

ADDCOLUMNS(

? ? Actual_Lit_Box,

? ? "Weekly Actual Pen",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? Actual_Pen_weekly,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? ) = FactSales1[branch] &&

? ? ? ? ? ? FactSales1[Week ID] =

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? )

? ? ? ? ),

? ? ? ? [Weekly Actual Pen]

? ? )

)

?

--in a separate table, I calculate quarterly targets (called total targets):

VAR Total_TBL =

ADDCOLUMNS(

? ? Adding_Actual_Weekly_Pen,

? ? "Total Penetration Target",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? DimBranchTarget,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? ) = DimBranchTarget[Branch]

? ? ? ? ),

? ? ? ? DimBranchTarget[Penetration]

? ? ),

? ? "Total Lit Target",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? DimBranchTarget,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? ) = DimBranchTarget[Branch]

? ? ? ? ),

? ? ? ? DimBranchTarget[Liters]

? ? ),

? ? "Total Box Target",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? DimBranchTarget,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? ) = DimBranchTarget[Branch]

? ? ? ? ),

? ? ? ? DimBranchTarget[Box]

? ? )

)

?

--Most interesting part, adding a column that cumulatively sums up weekly actual sales (Liters & Boxes)

VAR Cumulatives_TBL =

ADDCOLUMNS(

? ? Total_TBL,

? ? "Cumulative Lit",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? Total_TBL,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) <= FactSales1[Week ID] &&

? ? ? ? ? ? FactSales1[branch] =

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? )

? ? ? ? ),

? ? ? ? [Weekly Actual Lit]

? ? ),

? ? "Cumulative Box",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? Total_TBL,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) <= FactSales1[Week ID] &&

? ? ? ? ? ? FactSales1[branch] =

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? )

? ? ? ? ),

? ? ? ? [Weekly Actual Box]

? ? ),

? ? "Cumulative Pen",

? ? SUMX(

? ? ? ? FILTER(

? ? ? ? ? ? Total_TBL,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) <= FactSales1[Week ID] &&

? ? ? ? ? ? FactSales1[branch] =

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[branch]

? ? ? ? ? ? )

? ? ? ? ),

? ? ? ? [Weekly Actual Pen]

? ? )

)

?

--My metric to find rank of each week is their target rate, so I add new columns dividing actuals by targets:

VAR Calc_Weekly_Rate =

ADDCOLUMNS(

? ? Cumulatives_TBL,

? ? "Cumulative Pen Rate",

? ? DIVIDE(

? ? ? ? [Cumulative Pen],

? ? ? ? [Total Penetration Target]

? ? ),

? ? "Cumulative Lit Rate",

? ? DIVIDE(

? ? ? ? [Cumulative Lit],

? ? ? ? [Total Lit Target]

? ? ),

? ? "Cumulative Box Rate",

? ? DIVIDE(

? ? ? ? [Cumulative Box],

? ? ? ? [Total Box Target]

? ? )

)

?

--Now, I can calculate each branch ranking based on their target rate

VAR Adding_Ranking =

ADDCOLUMNS(

? ? Calc_Weekly_Rate,

? ? "Rank by Pen Rate",

? ? RANKX(

? ? ? ? FILTER(

? ? ? ? ? ? Calc_Weekly_Rate,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) = FactSales1[Week ID]

? ? ? ? ),

? ? ? ? [Cumulative Pen Rate],

? ? ? ? ,

? ? ? ? DESC

? ? ),

? ? ? ? "Rank by Lit Rate",

? ? RANKX(

? ? ? ? FILTER(

? ? ? ? ? ? Calc_Weekly_Rate,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) = FactSales1[Week ID]

? ? ? ? ),

? ? ? ? [Cumulative Lit Rate] ,

? ? ? ? ,

? ? ? ? DESC

? ? ),

? ? ? ? "Rank by Box Rate",

? ? RANKX(

? ? ? ? FILTER(

? ? ? ? ? ? Calc_Weekly_Rate,

? ? ? ? ? ? EARLIER(

? ? ? ? ? ? ? ? FactSales1[Week ID]

? ? ? ? ? ? ) = FactSales1[Week ID]

? ? ? ? ),

? ? ? ? [Cumulative Box Rate],

? ? ? ? ,

? ? ? ? DESC

? ? )

)

?

--I prefer to have a cleaned up table where everfy field of the table has a clear & to-the-point name, so I create one here:

VAR Final_TBL =

SELECTCOLUMNS(

? ? Adding_Branch_Grade,

? ? "Week Lag",

? ? FactSales1[Week Lag],

? ? "Branch",

? ? FactSales1[branch],

? ? "Grade",

? ? [Branch Grade],

? ? "Rank in Pen",

? ? [Rank by Pen Rate],

? ? "Rank in Lit",

? ? [Rank by Lit Rate],

? ? "Rank in Box",

? ? [Rank by Box Rate]

)

?

return Final_TBL

?

?

final visual can look like this: