Need Help Configuring Excel Data to make sense in Power BI

[Canknucklehead2]

Not sure if I'll be able to sort this out. We do not currently have a way of uploading a sales budget to our ERP, so I'm trying to link the sales data from the ERP to an Excel Spreadsheet that contains the budget information.

The excel file has a list of SKUs and then monthly columns with dollar values and then additional monthly columns with quantity values.

SKU     Jan$ Feb$ ...Dec$  JanQTY FebQTY ...DecQTY

ABC     $50   $75     $150    10       15            30

DEF      $20   $40     $20       5        10             5

I can split it into two sheets if necessary to separate QTY vs $, so that's a minor issue. 

What I am struggling with is how do I consolidate the columns yet maintain the month specific information so that I can use a date slicer to do comparisons? I can't load each individual column into a visual and then have it isolate March's actual sales vs budget. Yet if I create a custom column to sum it up (or a measure), it will give me an average and not the actual budgeted amount. Can it be done with the Excel Spreadsheet with some sort of Transform?

I know it would be better to simply upload the budget to the ERP, but that is an entirely different struggle that I'm dealing with elsewhere. I'm just hoping I can figure out a stopgap solution for the short term.

[v-zhouwen-msft · Accepted solution]

Hi @Canknucklehead2 ,

I assumed two tables of data.
‘Table1’: a list of SKUs (I only assumed 5 months of data)
‘Table2’: Daily sales data in EPR

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a table named ‘MonthTable’

MonthTable = GENERATESERIES(1,12,1)

2. Use the following DAX expression to create a measure named ‘Sales budget for the month’ (Please replace 'BLANK()' with the remaining months according to the expression)

Sales budget for the month = 
VAR _a = SELECTEDVALUE(MonthTable[Value])
VAR _b =SWITCH(TRUE(),
_a = 1,SUMX('Table1',[Jan$] * [JanQTY]),
_a = 2,SUMX('Table1',[Feb$] * [FebQTY]),
_a = 3,SUMX('Table1',[Mar$] * [MarQTY]),
_a = 4,SUMX('Table1',[Apr$] * [AprQTY]),
_a = 5,SUMX('Table1',[May$] * [MayQty]),
BLANK()
)
RETURN _b

3. Use the following DAX expression to create a measure named ‘Actual sales for the month’ (Please replace 'BLANK()' with the remaining months according to the expression)

Actual sales for the month = 
VAR _a = SELECTEDVALUE(MonthTable[Value])
VAR _b = SWITCH(TRUE(),
_a = 1 , SUMX(FILTER('Table2','Table2'[Month] = 1),'Table2'[Amount]),
_a = 2 , SUMX(FILTER('Table2','Table2'[Month] = 2),'Table2'[Amount]),
_a = 3 , SUMX(FILTER('Table2','Table2'[Month] = 3),'Table2'[Amount]),
_a = 4 , SUMX(FILTER('Table2','Table2'[Month] = 4),'Table2'[Amount]),
_a = 5 , SUMX(FILTER('Table2','Table2'[Month] = 5),'Table2'[Amount]),
BLANK())
RETURN _b

4. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.