How to count the number of instance outside of the current context please ?

[speedramps]

How to count the number of instance outside of the current context please ?

Use this Sales data

Record ID	Sales rep	Customer	Category	Product	Sales LY	Sales TY
1	Albert	Anne	TV	T42	20	null
2	Albert	Anne	TV	T42	30	null
3	Albert	Anne	TV	T62	null	50
4	Albert	Anne	Radio	R100	55	null
5	Albert	Anne	Kettle	K101	75	null
6	Albert	Anne	Phone	P130	null	88
7	Ben	Betty	Kettle	K315	87	null
8	Ben	Bella	Kettle	K101	null	2
9	Ben	Bella	Phone	P120	null	47
10	Chris	Charlotte	Phone	P120	75	null
11	Chris	Charlotte	Phone	P130	72	null
12	Chris	Charlotte	Radio	R120	null	null
13	Chris	Chloe	Radio	R100	null	94
14	Chris	Camila	Radio	R101	44	null
15	Chris	Camila	Radio	R500	null	31
16	Chris	Camila	TV	T42	50	25
17	Chris	Chloe	TV	T42	null	94
18	Chris	Camila	Phone	P40	null	33

Create Measures

Total Sales LY = SUM(Sales[Sales LY])

Total Sales TY = SUM(Sales[Sales TY])

Status =

IF([Total Sales LY]>0 && [Total Sales TY]=0, "Lost",

IF([Total Sales LY]=0 && [Total Sales TY]>0, "New",

IF([Total Sales LY] > [Total Sales TY], "Reduced",

IF([Total Sales LY] < [Total Sales TY], "Grew",

"Same"))))

The Problem

2 reports are required.

• One that will show the number of customers each salerep has started selling a new product category to.
• One that will show the number of customers each salerep has cease selling a product category to.

For example:-

• Albert has stopped selling Kettles and Radios to Anne
• Albert has started selling Phones to Anne
• Ben has started selling Kettles and Phones to Bella
• Ben has stopped selling Kettles and Phones to Betty
• Chris has started selling Phones to Camila
• Chris has stopped selling Phones to Charlotte
• Chris has started selling Radios and TVs to Chloe.

The reports should only count an instance of a customer once regardless of how many new or lost categories they have.

The New report should show by salesrep :-

• Albert has new categories for 1 customer 
• Ben has new categories for 1 customer (albeit for 2 categories)
• Chris has new categories for 2 customers (albeit for 3 categories)

The Lost report should show by salerep :-

• Albert has lost categories for 1 customer (albeit for 2 categories)
• Ben has lost categories for 1 customer
• Chris has lost categories for 1 customer

How do you do that please?

Here is a screen print example using Count(Distinct) on Customer,
which does not produce the required results because
it aggregates at the Salesrep level instead of the Category level.

So no New and Loss status shows on then "Sales Rep Level" report

[v-eachen-msft]

Hi @speedramps ,

You could use DISTINCTCOUNT() function.

Here is my test file for your reference, but I think Chris has lost categories for 2 customers in the Lost table.

[speedramps · Accepted solution]

Thank you for your help, I have now solved it.

Please post any ideas on easier ways.

This is my solution ….

Add measures to get the Customer product Category level totals

Total Sales LY for Customer Category =

CALCULATE  (

[Total Sales LY],

All (Sales),

VALUES(Sales[Customer]),

VALUES(Sales[Category])

)

Total Sales TY for Customer Category =

CALCULATE  (

[Total Sales TY],

All (Sales),

VALUES(Sales[Customer]),

VALUES(Sales[Category])

)

Add measure to get the status at Customer product Category level

Status at customer category level =

VAR LY =[Total Sales LY for Customer Category]

VAR TY =[Total Sales TY for Customer Category]

return

IF(LY>0 && TY=0, "Lost",

IF(LY=0 && TY>0, "New",

IF(LY>TY, "Reduced",

IF(LY<TY, "Grew",

"Same"))))

)

Then count the number of customers with a lost or new category

Customers with lost categories =
CALCULATE(DISTINCTCOUNT(Sales[Customer]),
FILTER(Sales,[Status at customer category level]="Lost"))

Customers with new categories =
CALCULATE(DISTINCTCOUNT(Sales[Customer]),
FILTER(Sales,[Status at customer category level]="New"))

Testing

• Albert has lost categories for 1 customer (albeit for 2 categories)
• Albert has new categories for 1 customer
• Ben has lost categories for 1 customer
• Ben has new categories for 1 customer (albeit for 2 categories)
• Chris has lost categories for 1 customer
• Chris has new categories for 2 customers (albeit for 3 categories)

See screen print

At record level Chris lost sales to Camila of product R101, but did sell some other Radios to Camila but not at much as last year.  Note how the columns show New and Lost at record level but Reduced at Category level, as required.

[amitchandak]

When you are comparing 0 sales. Can you also handle null value

(ISBLANK(Sales[Sales LYTD]) OR  Sales[Sales LYTD]) =0)

[speedramps]

Thanks you,  but I am not sure that helps with the above problem which uses measures?

• Total Sales LY = SUM(Sales[Sales LY])
• Total Sales TY = SUM(Sales[Sales TY]

I think the problem is about how to count the Status at a Category context level, whilst at Sales Rep level.  

Many thanks

[speedramps]

Here is example of the PBIX with the problem. 

https://1drv.ms/u/s!AgMTUY3Uvq3bgvNrZT7wU53lGRVX8g?e=bxB1m5