populate the sales value for the sales person representing client and excluding all other

[Anonymous]

Hello Community,
I am stuck at a point were i need to populate the sales amount for the sales person representing the company and exclude every other sales amount and sales person who made sales for the same company.

Thanks in Advanced

[tamerj1]

@Anonymous 

Here is the sample file with the solution https://www.dropbox.com/t/SCaRbdWWuQk8Kl90

The code I have provided you is working (at lease as per my understanding of the problem). Below is the same code modified as per the sample file. You can add time filters inside calculate to accommodate for your requirements.

Total Sales = 
VAR SalesAmount1 =
    SUM ( 'cust_sales'[Sales_LCY] )
VAR SalesAmont2 =
    CALCULATE (
        SUM ( 'cust_sales'[Sales_LCY]  ),
        TREATAS (
            VALUES ( 'cust_table'[Salespeople] ),
            'cust_sales'[Sales_Person_Name]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'cust_sales'[Sales_Person_Name] ), SalesAmount1,
        ISINSCOPE ( 'cust_table'[Salespeople] ), SalesAmont2,
        SalesAmount1
    )

[Anonymous]

Hello @tamerj1 ,
Thanks for providing the solution it works perfect as i needed to populate the data .

[tamerj1 · Accepted solution]

@Anonymous 

Apologies for the late reply. I got distracted by some other business. 
Please try the following code. Might not succeed from the first time. 

Total Sales =
VAR SalesAmount1 =
    CALCULATE (
        SUM ( 'Value Entry'[Dollar Sales] ),
        FILTER ( 'Calender Table', 'Calender Table'[Date] < TODAY () )
    )
VAR SalesAmont2 =
    CALCULATE (
        SUM ( 'Value Entry'[Dollar Sales] ),
        FILTER ( 'Calender Table', 'Calender Table'[Date] < TODAY () ),
        TREATAS (
            VALUES ( 'Customer Sales'[sales_lyc] ),
            'Customer Table'[Assignes_Sales_Person]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Customer Table'[Assignes_Sales_Person] ), SalesAmount1,
        ISINSCOPE ( 'Customer Sales'[sales_lyc] ), SalesAmount2,
        SalesAmount1
    )

[Anonymous]

Hello @tamerj1 ,
sorry for the late reply but my system was down I tried using above dax and it populate the value suming all the sales  value done for that client including other inculding sales of other sales person 


Actuall sales per sales person :

Actual sales per sales person
Thanks Again @tamerj1 

[Anonymous]

Hello @tamerj1 ,
Thanks for the reply but there is samll correction [sales lyc] and [total sales ] both are same .

[tamerj1]

@Anonymous 

Please reply to below in order to proceed with solution 

[tamerj1]

Ik can you please write the colum names again using this template 'Table'[Column]

[tamerj1]

Hi @Anonymous 

So what is issue here? You can slice sales amount by sales person but I did not understand the exclusion part

[Anonymous]

Hello @tamerj1 ,
Thanks for the instant reply. I have to populate the sales value for the sales person representing the particular company(client) and also i need to exclude all the sales made by other sales person to that same company(client) but i am not able to exclude.
Schema Description - all the sales values are coming from value entry, every sales person representing the client is coming from customer table .
Please let me know if you need more information 

Thanks in Advanced

[tamerj1]

@Anonymous 

So in your report, you are slicing by sales rep. or using hierarchy composed of company/sales rep. ? If you already calculated the amount of the company and the amount of the sales rep. then why don't you just subtract the later from the company amount?

[Anonymous]

I am representing the data in table visual and when i am including the fields there is client and below that the sales person representing the client and below that it includes other sales person who made sale for that client and i need to exclude them but unfortunately i am not able to do that  for more clarification i am attaching image

[Anonymous]

Hello @tamerj1 ,
we can see the actual sale of eric nachbar is 3,47,082 but without expanding it populate sum of all sales done by all sales person for that client .
Please let me know if you need more information .
Thanks in Advance 

[tamerj1]

@Anonymous 
can you please share the code for total sales?

[Anonymous]

@tamerj1 
Total Sales = CALCULATE(SUM('Value Entry'[Dollar Sales]),FILTER('Calender Table','Calender Table'[Date]<TODAY()))

[tamerj1]

@Anonymous 
It should be possible we can use SWITCH along with ISINSCOPE to control the caculation at each hierarchy level. We can combine that with TREATAS to obtain the desired results. In order to do that I need the column and table names of all the columns involved in the hierarchy. Thank you

[Anonymous]

Hello @tamerj1 ,
Name of Customer = customer sales [customer_name]
sales amount = customer sales [sales_lyc]
Assignes sales person =  customer table 

Thanks if need more information let me know 
Thanks 

[Anonymous]

Hello @tamerj1 ,
Please let me know if you need more information .

Thanks in advanced 

[Anonymous]

Hello @tamerj1 ,
Thanks for the instant reply. I have to populate the sales value for the sales person representing the particular company(client) and also i need to exclude all the sales made by other sales person to that same company(client) but i am not able to exclude.
Schema Description - all the sales values are coming from value entry, every sales person representing the client is coming from customer table .
Please let me know if you need more information 

Thanks in Advanced