Calculate Total Sales Revenue by Territory for Customers that share inventory across Territories

[Anonymous]

Hello,

I am new to Power BI and am having trouble solving the below problem. I will explain as best I can, but if further detail is needed please let me know and I will provide it. Any assistance is appreciated!

We have Sales Reps assigned to a single Territory. In some Territories there are Customers that have a Parent-Child relationship where only the Parent location can purchase inventory, but the Parent and Child locations can use it. When the Parent and Children locations are in different Territories it creates a problem when calculating commission for each Customer. In these cases, each Customer is assigned an "Allocated Revenue" based on the inventory used at each location per month.

Example Scenario: 

In Power BI I have a Sales Fact Table that has a row for each line item on all of our invoices. On Reports I am showing a SUM of the "Real Revenue" per Territory in a given date range, but I need to calculate the "Allocated Revenue" seen above and use that in place of the "Real Revenue" for Customers that share inventory across Territories. This way when I SUM up all the numbers the "Allocated Revenue" is used as needed and the Revenue is spread across each Territory appropriately.

How can I calculate the "Allocated Revenue" for each row in the Fact Table and use it in place of the "Real Revenue" where appropriate?

There are columns in the Fact table that indicate when customers share inventory and who they share it with (isShared and ParentID), and I have the details on the number of units used by each customer every month in another table.

Thanks in advance!

[Nathaniel_C]

Hi @Anonymous ,

So are you saying if a certain condition is met, sharing, then used Allocated Revenue, else use Real Revenue?

Maybe show us what you expect to see for each line on your table?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hi @Nathaniel_C, 

Thanks for the reply, much appreciated. When you put it like that it sounds like a simple SWITCH statement would do the job, but I'm not sure that's it because I need to spread the "Real Revenue" across all the Child Customers. This needs to happen at the ROW level and roll up to the visual where Total Sales is displayed by Territory with filters on YTD, QTD, and MTD.

The problem is that I need to calculate the value for the "Allocated Revenue" above because it doesn't exist in the Fact Table. Then when I SUM up all the Sales in the Fact Table to get totals for the visual, I need the "Total Sales" to be a combination of "Real Revenue" (for Customers that don't share inventory) + "Allocated Revenue" (for Customers that do share inventory). That way the "Real Revenue" for the Parent that made the purchase is recognized and spread across the appropriate Territories based on the number of units they used.

[Nathaniel_C]

Hi @Anonymous ,

Would you give us an example of input and output?  It may help us understand the issue.

You have said: The problem is that I need to calculate the value for the "Allocated Revenue"  so what is that calculation in mathmatical terms? Maybe a picture of your model might be useful too, if you can share that.

Thanks,

Nathaniel