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.
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:
If you still have questions after taking a look, just give a shout.
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.
Hope this is helpful.
eDNA Forum - SUMX Allocation Solution.pbix (32.2 KB)
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.
Good luck with your project. Just give a shout if you have any questions or run into any problems.
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):
- in the filter pane for the table visual, I filtered out the “Other” row:
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" )
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] ) )
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.
I hope this is helpful.
eDNA Forum - SUMX Allocation Solution v2.pbix (32.3 KB)
Thank you Brian, you solved it for me