products with different values/budget allocations

[cvandenbro]

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!

[Jai-Rathinavel · Accepted 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 🙂

[Anonymous · Accepted solution]

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

[Jai-Rathinavel · Accepted 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 🙂