Allocating sales with 2 conditions applied

[NirH_at_BITeam]

Hi All,

Here is the challenge;

I have a customer who would like to run a sales model but he has two conditions that I need to apply for calculating the sales per rep, per channel and per product correctly:

The idea is to allocate evenly the total sales that were defined “Wholesaler” between all reps, (for the sake of simplicity he decided that even allocation should be fine enough).

The second condition is to exclude any rep that is defined [Wholesaler Rep] = “False, which in our case currently it is 10 reps overall instead of 11.

So basically what I’m required to implement is dynamically calculate the sum of: (275,830 + 882 + 8,887 + 14,362 = 299,961) divide it by 10 and the result allocate back to each rep (including those who are “Wholesaler” ) but except the one who is “False”.

Any assistance would be highly appreciated!

https://nirh1153862-my.sharepoint.com/:u:/g/personal/nir_biteam_co_il/Ee6KymtOap9NpviHvEKjwt8Bh_M2H8...

https://app.powerbi.com/view?r=eyJrIjoiYmRjM2ZmNDgtZjI4Ny00OTcwLTg1NjctNmNkZDg2NzE0ZDlmIiwidCI6IjVjM...

Thanks!

NH

[v-qiuyu-msft · Accepted solution]

Hi @NirH_at_BITeam,

Please create a measure below: 

Measure = IF(SELECTEDVALUE(Reps[Wholesaler Rep])=TRUE(),DIVIDE(CALCULATE(SUM(Sales[Actual Sales Converted]),FILTER(ALLSELECTED(Customers),Customers[Sales Allocation]="Wholesaler")),CALCULATE(COUNTROWS(Reps),FILTER(ALL(Reps),'Reps'[Wholesaler Rep]=TRUE()))),BLANK())

Best Regards,
Qiuyun Yu 

[NirH_at_BITeam]

Thank you Qiuyun Yu!

This seems to work but only partially and I believe that my client would like to restructure the AN_Sales[Volume Sales] per the new allocation and not just creating a measure.

In other words I think the idea was to recreate a revised [Volume Sales] under the AN_Sales which will represent the new allocation methodology per customer per product.

Pls. note that creating the measure you suggested didn’t allow to reaggregate the data per Chanel or per Products, or did I miss something.

I wrote it is a challenge 😊

Best,

NH