Latest Enterprise DNA Initiatives

Allocate a Field value based on % ratio (from different Field)

Hi E.DNA members:

Hopefully the chart below illustrates what I’m trying to achieve, showing Before and After. I’m seeking to allocate the specific ‘Category C’ value of 900 shown below (Field B and A intersect) – based on the % ratio derived from the ‘Rev’ value (Field A). FWIW, the allocation would be performed for every month period.
Thanks in advance.

Before I attach the solution file, please note, I have taken you at your word - that this solution needs to deal with a specific category in your data. If this needs to be a dynamic solution (changing between categories), it’s going to take a LOT more puzzling :slight_smile:

the key measure in this solution is “Modified Cost”

image
Notice that I have branched off of other measures, all of which are included in the attached fileeDNA - handling specific field value calculation.pbix (21.1 KB)

1 Like

Thank you Heather, wow. I will study your recommended solution and let you know if I can apply it to my data model. Note that in my model, “Original Cost” and “Total Revenue” are actually the two possible values within a single field or column “A”, not two separate fields per your .pbix file. How would that change the recommended solution?

The solution only changes in how you calculate the base measures for Original Cost & Total Revenue. These need to be wrapped in a CALCULATE to determine which value you are looking for in your measure. For my example, I assume that there is a column on your data table that indicates if the value is cost or revenue, and then used CALCULATE to filter to that value:

image

image

A note for future questions, it would be helpful to provide a simplified copy of your report, with the tables as they are set in your original file. I would not have guessed from your base question that you had a single column with cost & revenue combined. :smiley: And you would have had your final response a bit faster.

my mockup is attached, with a new “Data (2)” table added that demonstrates this solution.

eDNA - handling specific field value calculation - round 2.pbix (25.2 KB)

Hi @mdalton2100, did the response provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @mdalton2100, we’ve noticed that no response has been received from you since the 12th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Moderator: I have not had time to study and apply the solution. I will advise in next week, thanks.

Hello Heather,

I have finished reviewing your solution, it will work fine. Now that I can calculate and apply the allocation, I’d like to merge the “Modified Cost” and “Total Revenue” measure into a single measure, in order to present a gross margin (or P&L view, by category) layout. Of course that requires negating the sign for Value where the Attribute = Cost, which I have done in revising your attached Pbix file (as a new measure).

Modified Cost and Total revenue

However, now I have the challenge of calculating this supplemental measure, so that I can achieve this view. Below shows how the view would look vertically, but of course I have not figured out how to present true Revenue (grossed up = $1,525) and Cost (after the allocation = ($2,850).

gross margin layout

eDNA - handling specific field value calc - round 2b (modify value sign for Cost).pbix (27.1 KB)

as we have now ventured into a new issue, I suggest opening a new question - perhaps with this linked to it as history for other users.

I am not overly familiar with the intricacies of the P&L layout (I don’t do accounting style reports very often), so I’m sure you’ll capture the attention of one of our other members better with a new post. :slight_smile:

1 Like

Thanks again, Heather! Will do.