Allocating a total value in column based on proportional values in another column

I would like to allocated the 2,000 value in Column B, based on Sales Mix values in Column A, see below. I hope this is enough information, I am fairly new to the Forum.

@Floyd,

Welcome to the forum!

SUMX is a great tool for this type of calculation. Here are a couple of excellent videos explaining it and other similar iterating functions:

Sumx/averagex/minx/max

If you still have questions after taking a look, just give a shout.

  • Brian

@Floyd,

Now that I’m back at my computer, I put together a solution file for you.

image

Here’s the key measure using SUMX:

 Target Sales Allocated = 

SUMX(
    Sales,
    [Sales to Allocate Value] * [Sales Mix]
)

Definitely still check out the videos above - tons of great info there.

Hope this is helpful.

Thank you Brian. I am quite new-ish to Power BI, so am just working through the example that you kindly attached and busy applying the logic to the issue I am having. Will be in touch once I get it all (hopefully) working on my side.

Steve

@Floyd,

Good luck with your project. Just give a shout if you have any questions or run into any problems.

  • Brian

Hi Brian

I have managed to get some of the way there. I have successfully created 2 of the columns below, but I can’t create the last one. I see that you created a What-If Parameter field which you populated with 2,000. The number (2,000) I need to reallocate actually resides within the data in a row called ‘Other’, under ‘Hot Dogs’.

Is there a way I can reference where ‘Column A Actual Sales’ = ‘Other’ and then use that as opposed to the What-If Parameter field ?

@Floyd,

Absolutely. I’m assuming based on the screenshot you provided, you don’t want to add a slicer by Product to your report. That’s definitely one way to set some of the filter conditions needed, but the solution provided below is sans slicer.

Here are modifications I made to the original solution file provided (new solution file attached below):

  1. in the filter pane for the table visual, I filtered out the “Other” row:

image

  1. created the following measure to capture the total value of the Other row to be allocated:

     Target Sales to Allocate = 
    
     CALCULATE(
         [Total Actual Sales],
         Sales[Product] = "Other"
     )
    
  2. now we have to change the measure to calculate the denominator of our sales mix % from ALL to ALLSELECTED, since we want to maintain the filter on Other, otherwise our denominator will include the additional $2,000:

     Total Actual Sales ALLSELECTED = 
    
     CALCULATE(
               [Total Actual Sales],
         ALLSELECTED( Sales[Product] )
     )
    
  3. and finally, change our Target Allocation measure to incorporate the value we pulled in from the Other row in 2) above

     Target Sales Allocated = 
    
     [Target Sales to Allocate] * [Sales Mix]
    

Here’s how it all looks put together - I added a card visual just to show the $2000 value to be allocated, but it’s not necessary and can be deleted.

image

If you haven’t already, I would highly recommend working your way through this course, particularly the sections that focus on evaluation, filter, and row context and the CALCULATE and FILTER functions.

I hope this is helpful.

Thank you Brian, you solved it for me :slight_smile: