Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
cvandenbro
New Member

products with different values/budget allocations

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:

 

cvandenbro_0-1731677968697.png

 

 

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.

 

cvandenbro_1-1731677968698.png

 

 

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!

 

2 ACCEPTED SOLUTIONS
Jai-Rathinavel
Super User
Super User

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. 

JaiRathinavel_2-1731696771489.png

 

After unpivotting your table should be like this

 

JaiRathinavel_3-1731696822196.png

 

Now you can visualize this data in a front end table visual as below

JaiRathinavel_4-1731696933479.png

Did I answer your question ? If yes, please mark my post as a solution. 

 

Thanks,

Jai 🙂

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

v-cgao-msft
Community Support
Community Support

Hi @cvandenbro ,

 

Please refer to the following relationship model:

vcgaomsft_0-1731899681058.png

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:

vcgaomsft_1-1731899734516.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @cvandenbro ,

 

Please refer to the following relationship model:

vcgaomsft_0-1731899681058.png

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:

vcgaomsft_1-1731899734516.png

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
Super User
Super User

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. 

JaiRathinavel_2-1731696771489.png

 

After unpivotting your table should be like this

 

JaiRathinavel_3-1731696822196.png

 

Now you can visualize this data in a front end table visual as below

JaiRathinavel_4-1731696933479.png

Did I answer your question ? If yes, please mark my post as a solution. 

 

Thanks,

Jai 🙂

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.