Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
I’ve just started using powerBI and I’ve got the basics going, but this one it a bit more complicated than the course that I’ve followed.
I’ve got a list of products that are linked to budget numbers. Most of the time it’s 1 product = 100% allocated to a certain budget.
However, I’ve got a couple of products that can be linked to up to 5 different budgetnumbers (each budget would then have a certain percentage, in total 100% of the cost is divided over these budget numbers).
This is a simplified example:
The idea would be that the owner of the budget would be able to click/filter on their budget and see only what has been charged on their budget (and this per year)
Let’s say product A could sold 1000 times and for each sold item 1 euro is charged over the different budgets. Budget “ABC” would then be charged 300 euro’s and “DEF” 700 euro’s.
In example below of sales
sales date | product | sold | production cost | total cost | ABC | DEF | GHI |
15/11/2024 | product A | 1000 | 1,00 € | 1.000,00 € | 300,00 € | 700,00 € | |
01/09/2024 | product B | 500 | 1,00 € | 500,00 € | 500,00 € | ||
15/11/2023 | product A | 500 | 1,00 € | 500,00 € | 150,00 € | 350,00 € | |
15/11/2023 | product B | 2000 | 1,00 € | 2.000,00 € | 2.000,00 € |
I should get this in power BI (and should be able to filter on the year)
Budget | budget used |
ABC | 450,00 € |
DEF | 1.050,00 € |
GHI | 2.500,00 € |
How would that work? Based on what I’ve found I should use VAR, FILTER, …?
Thanks a billion!
Solved! Go to Solution.
Hi @cvandenbro ,
To achieve the expected output. You can unpivot the selected columns "ABC", "DEF" and "GHI" at a power query level like the below example.
After unpivotting your table should be like this
Now you can visualize this data in a front end table visual as below
Did I answer your question ? If yes, please mark my post as a solution.
Thanks,
Jai 🙂
Proud to be a Super User! | |
Hi @cvandenbro ,
Please refer to the following relationship model:
And create a new measure:
Budget used =
VAR __sold = CALCULATE( SUM('Sales'[sold]), CROSSFILTER('Budget'[product],'DimProduct'[product],Both) )
VAR __allocation = MAX('Budget'[allocation])
VAR __result = __allocation*__sold
RETURN
__result
Output:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @cvandenbro ,
Please refer to the following relationship model:
And create a new measure:
Budget used =
VAR __sold = CALCULATE( SUM('Sales'[sold]), CROSSFILTER('Budget'[product],'DimProduct'[product],Both) )
VAR __allocation = MAX('Budget'[allocation])
VAR __result = __allocation*__sold
RETURN
__result
Output:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @cvandenbro ,
To achieve the expected output. You can unpivot the selected columns "ABC", "DEF" and "GHI" at a power query level like the below example.
After unpivotting your table should be like this
Now you can visualize this data in a front end table visual as below
Did I answer your question ? If yes, please mark my post as a solution.
Thanks,
Jai 🙂
Proud to be a Super User! | |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
50 |
User | Count |
---|---|
211 | |
87 | |
80 | |
69 | |
60 |