# 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.

1 Like

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:

https://forum.enterprisedna.co/t/sumx-averagex-minx-max/3278

https://forum.enterprisedna.co/t/iterating-functions-deep-dive-sumx-averagex-minx-maxx/2993

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

• Brian

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

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.

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

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 ?

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:

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.

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.