Actual versus projected

[JMWDBA]

So I have two files:

1.  Mock Projections https://drive.google.com/file/d/1hpZdZEbsuO0fyH3s_u6e_6z_3Xn8fpfc/view?usp=sharing
2. Mock Actual Sales https://drive.google.com/file/d/1kIiXhVDBMeRdwca88I7kJetAelFsJo4h/view?usp=sharing 

The Mock Projections file contains the sales data by store location by month in the fiscal year.  The Mock Actual Sales shows the actual sales by store location by month in the fiscal year. 

What I need to do is the following:

1. Calculate the variance between Projected and Actual Sales by Month, then
2. Calculate the variance between Projected and Actual Sales for the fiscal year

How do I set this up in Power BI?

[stretcharm]

Load both data sets.

Then for both data sets apply these rules

Unpivot the Months

Create a date from the Month and Year

Create a Key from the Date and Store Code

Merge the data and expand the value frm the projects so you end up with a rows like this

Here is the M Advanced Editor Scripts for the Actual and Combined Results. (Projection is the same as Actuals but with a different data)

let
    Source = Excel.Workbook(File.Contents("L:\Downloads\Mock Actual Sales.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Store Name", type text}, {"Store Code", Int64.Type}, {"Fiscal Year", Int64.Type}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Store Name", "Store Code", "Fiscal Year"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Date", each "01/"&[Attribute]&"/"&Number.ToText([Fiscal Year])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type1", "Store Code", "Store Code - Copy"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Duplicated Column1", "Date", "Date - Copy"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Store Code - Copy", type text}, {"Date - Copy", type text}}, "en-GB"),{"Store Code - Copy", "Date - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"StoreDateKey")
in
    #"Merged Columns"

let
    Source = Table.NestedJoin(Actual,{"StoreDateKey"},Projections,{"StoreDateKey"},"Projections",JoinKind.LeftOuter),
    #"Expanded Projections" = Table.ExpandTableColumn(Source, "Projections", {"Value"}, {"Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Projections",{{"Value.1", "Projected"}})
in
    #"Renamed Columns"

Now everything is together you can easily add any calcs at month or year in DAX.

https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax#step-2

There is a nice visual called Power KPI Matrix which does nice comparisons of this sort of data.

https://powerbi.microsoft.com/en-us/blog/balanced-scorecards/

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381299?src=office&tab=Overview

[JMWDBA]

So here is my conundrum.  To create the table of "Actual Sales" I had to create a new table using the following:

Actual Sales by Location = SUMMARIZE('Actual Sales','Actual Sales'[Store Location], 'Actual Sales'[Sales Month], "Actual Sales", COUNTA('Actual Sales'[Sales Receipts]) )

Since this does not appear on the query side that would mean I cannot run an M Script or am I missing something?

[stretcharm · Accepted solution]

Correct you cannot tabe the summarize back into the query side. Howover you can take the query that feeds the 'Actual Sales' on the query side and do the summary in M using group by.

You can also merge data sets together into a new table in DAX. Get your data at the same level and join with a key then use one of the joining functions like NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>) .

e.g.

Sales by Location = NATURALINNERJOIN('Projected Sales by Location', 'Actual Sales by Location')