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

DAX DAX Calculations
1

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.

PBI
PBI.png765Ɨ274 8.13 KB

1 Like
2

@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:

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
3

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

4

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

5

@Floyd,

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

  • Brian
6

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 ?

Presentation1
Presentation1.jpg1280Ɨ720 35 KB

7

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

8

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