The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Community,
My boss has asked me to replicate a report that she currently does in Excel. The process involves downloading (at start of each month) the forecast data from our CRM. Our CRM does not capture "snapshots", so these downloaded files act as those. Which means, there is a file for Jan....for Feb....for March...etc
She compares what was forecasted at the start of each month, to what was actually ordered. The "ordered" data comes from our ERP and it is a typical transactional Fact table....with values by row for each day's orders.
Here is the tricky part: to calculate January's accuracy, she grabs the forecasted value from a tab (sheet) in her workbook. Example below. So the $200,000 value is coming from the 'Jan 19 Detail' sheet in her workbook. The "Actual" value is cut and pasted from a different workbook. For February, she would grab the CRM forecast data from the 'Feb Detail' sheet. She puts all the data in a table, and creates a chart visual (with months on the X axis). So imagine the information below for each month of the year laid out in table visual. She references these values for her chart.
Because each month's calculation is unique to that month's forecast, and she references "sheets" in her workbook to grab those values.....how to emulate this in Power Bi? I have imported each of her monthly files into Power Bi, but am perplexed as to how to go about emulating her "reference the sheet for each month" process. And in particular, how to use each month's specific values to do the calculations. In other words, I have to compare the forecast value in Jan's file to our ERP actuals.....then compare the forecast value in Feb's file to our ERP actuals....etc etc. But the, for example, how to display those totals in a chart visual.
Any advice is much appreciated!
='Jan Detail'!F14
Jan Jan Jan
FCST Actual Accuracy
$200,000 190,000 95%
You need to bring data together. Create common tables/dimension table. date or month table and analyze together.
Can you share sample data and sample output.
Additional information:
This is how the table in Excel would look (but there would all 12 months across the top). Each month's FCST total would come from a specific "sheet" in the Excel file. (the values from those sheets actually come from a workbork per each month). I have imported each of those month's files. You can see this below.
I need to compare the Jan FCST sum (Potential) to the actual order value for that month. sum of potential / sum of orders
Same for Februrary...but the FCST information comes from a different source file sum of potential / sum of orders
etc.....
My expected output would be to create a table of Jan's FCST, Feb's FCST.
Forecasted VS Actual Revenue | Feb FCST | Feb ACTUAL | % |
ERP Orders | $ 3,151,542 | $ 2,191,630 | 70% |
80% FCST | $ 1,171,291 | $ 798,109 | 68% |