topic Re: populate the sales value for the sales person representing client and excluding all other in DAX Commands and Tips

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

Anonymous — Thu, 28 Apr 2022 10:16:30 GMT

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

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

tamerj1 — Thu, 28 Apr 2022 10:28:44 GMT

Hi Anonymous

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

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

Anonymous — Thu, 28 Apr 2022 10:38:53 GMT

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

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

Anonymous — Thu, 28 Apr 2022 10:38:56 GMT

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

tamerj1 — Thu, 28 Apr 2022 10:57:41 GMT

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?

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

Anonymous — Thu, 28 Apr 2022 11:11:46 GMT

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

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

Anonymous — Thu, 28 Apr 2022 11:14:27 GMT

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

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

tamerj1 — Thu, 28 Apr 2022 11:26:23 GMT

Anonymous
can you please share the code for total sales?

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

Anonymous — Thu, 28 Apr 2022 11:28:42 GMT

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

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

tamerj1 — Thu, 28 Apr 2022 11:42:10 GMT

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

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

Anonymous — Thu, 28 Apr 2022 11:54:12 GMT

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

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

Anonymous — Thu, 28 Apr 2022 12:17:18 GMT

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

Thanks in advanced

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

tamerj1 — Thu, 28 Apr 2022 13:05:41 GMT

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 )

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

Anonymous — Thu, 28 Apr 2022 13:13:27 GMT

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

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

tamerj1 — Thu, 28 Apr 2022 13:27:58 GMT

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

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

tamerj1 — Thu, 28 Apr 2022 20:39:23 GMT

Anonymous

Please reply to below in order to proceed with solution

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

Anonymous — Fri, 29 Apr 2022 06:22:25 GMT

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 :

Thanks Again tamerj1

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

tamerj1 — Fri, 29 Apr 2022 13:34:45 GMT

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 )

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

Anonymous — Mon, 02 May 2022 05:17:16 GMT

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