Forum Discussion
How to visualize Ordering towards Forcast
Hello,
I work in a Maintenance department and I've build a PowerBI to visualize and monitor our budget. Where does our money go to? What type of interventions? Which suppliers? ... The dashboard is updatet on a weekly basis and we look at the spends that have been made last week and so far in 2024.
I've got a pretty decent visualization so far, but I'm stuck on the following issue. We made a forcast for our main suppliers (~60) that estimate in which week we will spend our money on certain orders. I already got a good visualisation of our total spends in comparison with the total forcast, but I'm not able to easliy visualize on which suppliers we spend more or less than the supplier forcast and how much it deviates (for example %-based). I was able to make a unique list of my suppliers and calculate the total spend with a calculated column based on SUMX, but i'm not able to load in the forcast value because LOOKUPVALUE doesn't work with dynamic columns. The names of our suppliers in our Spends and Forcast file are the same.
Can someone help me to easily visualize the deviation of the suppliers spends in comparison with supplier forcast (on a individual level)? Below you can find some parts of my data and the full table in code.
Part of ForCastBudget2024
| Date | Weeknummer | Total Forcast | Supplier A | Supplier B | Supplier C | Supplier D | Supplier E | Supplier F | Supplier G |
| 1/01/2024 | 1 | 33.171 | 2.385 | 192 | 192 | 192 | 192 | 385 | 0 |
| 8/01/2024 | 2 | 66.343 | 4.769 | 385 | 385 | 385 | 385 | 769 | 0 |
| 15/01/2024 | 3 | 99.514 | 7.154 | 577 | 577 | 577 | 577 | 1.154 | 0 |
| 22/01/2024 | 4 | 132.685 | 9.538 | 769 | 769 | 769 | 769 | 1.538 | 0 |
| 29/01/2024 | 5 | 165.857 | 11.923 | 962 | 962 | 962 | 962 | 1.923 | 0 |
| 5/02/2024 | 6 | 221.189 | 14.308 | 1.154 | 1.154 | 1.154 | 1.154 | 2.308 | 0 |
| 12/02/2024 | 7 | 276.521 | 16.692 | 1.346 | 1.346 | 1.346 | 1.346 | 2.692 | 0 |
| 19/02/2024 | 8 | 331.854 | 19.077 | 1.538 | 1.538 | 1.538 | 1.538 | 3.077 | 0 |
| 26/02/2024 | 9 | 387.186 | 21.462 | 1.731 | 1.731 | 1.731 | 1.731 | 3.462 | 0 |
Part of Spends2024 Table
| SC Number | Supplier | Description | Order date | order month | Ordering | GR |
| 1001810384 | Supplier A | AWT - Interventies op LPO | 20/01/2023 | € 4.635,30 | € 4.635,30 | |
| 1001804965 | Supplier B | MDO Move - Magazijninspectie 2023 | 4/01/2023 | 2023 | € 750,00 | € 750,00 |
| 1001811699 | Supplier C | SMC - 5-poortsventielen | 25/01/2023 | 2023 | € 1.023,12 | € 1.023,12 |
| 1001820477 | Supplier A | ATS - Ups scale ST40 met deur (deel 2 - 70%) | 20/02/2023 | 2023 | € 8.418,95 | € 8.418,95 |
| 1001843230 | Supplier E | Air Hendrickx - Huur en verplaatsing compressor | 3/05/2023 | 2023 | € 539,00 | € 539,00 |
| 1001855307 | Supplier A | Motoren Françoys - Motor 0,55kW 1500tr | 14/06/2023 | 2023 | € 463,77 | € 463,77 |
| 1001878666 | Supplier B | Serac - Drain valve | 6/09/2023 | 2023 | € 1.830,00 | € 1.830,00 |
| 1001879479 | Supplier C | KaiserKraft - MBA-slot | 8/09/2023 | 2023 | € 195,00 | € 195,00 |
| 1001880455 | Supplier I | Codipack - Mousse rol | 13/09/2023 | 2023 | € 706,16 | € 706,16 |
2 Replies
- lbendlin
Super User
The data in your Forecast sample table doesn't add up. You will also want to avoid having suppliers as columns. Unpivot the data to bring it into a usable format.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hey Ibendlin,
Thank you for the information and the time you've taken to process my question. I really appreciate it! I'm new in this and I was struggling with putting my data to my question. Thanks for the guide to add data! 🙂
To come back to your remarks:
1) The data in the Forcast file is the cumulative spend of 1 supplier. If $100 is spend in W2 then the amount is $100, if another $100 is spend in W3 the amount will corresponds with $200.
2) Will unpivot help in this case? I originally wanted to tackle the problem with a kind of INDEX-MATCH approach (Excel) to PowerBI, but discovered that the LOOKUPVALUE doesn't work with dynamic columns. So I'm stuck. I'm open for any suggestion.
Here is my sample data:
What I need:
1) We do a weekly update on Monday. We're in W9 so we want to check how the spends on each supplier deviates from the forcast (spends on 1 supplier from W1 to W8 and compare it with the cumulative spend of W8 from the forcast).
2) For example, for supplier K => SUM is calculated
3) Compare it with the value of W8 from supplier K => Add a "Deviation" column so the biggest deviations can easily be visualized.
I hope my question is clear 🙂
Thanks in advance!
Kind regards,
Jelse
