Need Help Configuring Excel Data to make sense in Power BI

Question

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.

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
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