cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Challenging Task - redistribution of Sales values

Hi Everyone,

 

This is a tough one I cannot manage to solve, so I'll try to explain short and sweet and thanks for any input in advance.

 

In short, I'm trying to redistribute the company sales (internal) values between 3 subgroups, based on how a customer locally distributed the purchased goods. The problem is that there is no direct relationship, since the customer uses internal references and some of them is not even an existing account in the internal system. This is 1 customer group, and there are other customers as well.

 

The redistribution should happen on month and subgroup level, which means on row level I get a % from the division of sales value and the total of the sales for that group in the given month (calculate(sum(sales),filter(subgroup = earlier(subgroup),filter(month = earlier(month))). 

 

Then this % should split the sales from the internal sales table (that is also on a subgroup and month level), so for example, if customer X in subgroup 1 had 2% (external) in 2018 JAN then I'd like to multiply (internal) Y sales value of subgroup 1 in 2018 JAN by 0.02

 

external performance (based on external sales) for account X by subgroup by month = 2%

internal performance by subgroup by month = 1000

required value = 1000 * 0.02 = 20

 

Below are some related snapshots and I can also share a sample PBIX file, if needed. The live data is also arranged like this, apart from 1 difference - customer sales is filtered by the date table, the relationship is not bi-directional.

 

So I'm a bit stuck with this. I managed to get the % values (by month by  subgroup), but I'm not sure how could I split up the internal sales values using the percentages, as there is no direct relationship.

 

I really appreciate any help and thanks for reading.

 

DM.JPGCol1.JPGCol2.JPGCol3.JPGCol4.JPG

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Challenging Task - redistribution of Sales values

Hi @DataIsLife ,

From your description, I want to know how could you get the 2%:

external performance (based on external sales) for account X by subgroup by month = 2%

And where could you want to show your desired result? In a table or a measure by filter?

Please offer your pbix that I could test for you and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors