cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DBAngst Frequent Visitor
Frequent Visitor

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

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: Sales.jpg

 

 

 


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!

3 REPLIES 3
Super User IV
Super User IV

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

Hi @DBAngst ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DBAngst Frequent Visitor
Frequent Visitor

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

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.

Super User IV
Super User IV

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

Hi @DBAngst ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors