Forum Discussion

Konrad_Schargel's avatar
Konrad_Schargel
Frequent Visitor
2 years ago

Total sales changes deppending on the weight selected for a rep and territory.

Hello all, 

 

I am tricking with a problem in PBI and I don't find the way to do it. 
The situation is the following.
 

My model consists of:
1. Sales table per Bricks (small territories). A group of bricks make up the territories.
2. Table of Territories.
3. Brick to Terr. Table that assigns each territory to a sales rep. There are territories that are shared between two sales reps.
4. Table of sales reps.

When distributing sales, in the territories that are shared, it may be necessary to assign a weight to that territory for that delegate. For example, the Pieria territory is shared between delegate GR-1 and GR-10. In this case we want the weight to be 0 for GR-1 and sales not to be counted in its territory, but there may be cases where a weight of 0.5 must be given to each delegate so that they are divided in half. The rest of the territories would have a weight of 1.

I have created an additional table called using DAX measure CROSSJOIN (Brick to terr and the weights) in order to have a table with the reps by brick with the three differents weigths (0,0,5,1).

 I need a DAX formula in which, using a slicer, I can assign a weight to a territory of a delegate. In this example the expected result should be that using the slicer (from the crossjoin table) I can choose that for the rep GR-1 the weight is 0 to PIERA territory and the sum of sales exclude this value. 



 

Can you help me? 

 

Thank you very much

Regards 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Konrad_Schargel ,

    I'm not clear about your requirement. Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and 1-2 special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards