Sales per customer with classification/area assignment (total/%, cluster groups, and running total)

[tbones]

Hello and thank you so much in advance for your help. This is melting my brain since several days and I can't figure out how to solve...

First of all what is needed:

- number of "qualified" sales per customer classification (in total and in %)

- number of customers per classification

- number of customers per classification with at least one "qualified" sale (in total and in %)

- number of customers per classification with at least 2, 3, 4 "qualified" sales (in total and in %)

- running total of "qualified" sales per customer classification per week/month

my data:

Sales table:

ID	EmployeeID	CustomerID	Date	Qualified_Sale
1	12345	123	01.01.2020	1
2	23456	234	10.09.2019	0
3	34567	345	05.07.2020	1

Qualified Sale: "1" stands for a "qualified" sale and "02 for non-qualified.

Customer table

ID	CustomerID	Classification	AreaID
1	123	A1	area1
2	123	A2	area1
3	234	A1	area2

CustomerID is non-unique

Classification is non-unique

AreaID is non-unique

Sales manager table

EmployeeID	AreaID	SalesManagerID
12345	area1	SM1
23456	area2	SM1
34567	area3	SM2

EmployeeID is unique

AreaID is unique

SalesManagerID is non-unique

additionally I have a Calendar table

relations between tables:

Sales Manager table -> Sales table, 1:n using EmployeeID

Sales Manager table -> customer table, 1:n using AreaID

what I have so far:

- Total Sales = COUNTROWS(Sales)

- Total Customers = DISTINCTCOUNT(Customers[ID])

- Total Qualified Sales = CALCULATE([Total Sales], Sales[Qualified_Sale] = 1

- Total Qualified Sales Per Customer = CALCULATE([Total Qualified Sales], FILTER(Sales, CONTAINS(Customer, Customer[ID], Sales[ID]))

- Total Customer With Qualified Sale  = CALCULATE(DISTINCTCOUNT(Customers[ID]), , Sales[Qualified_Sale] = 1, FILTER(Sales, CONTAINS(Customer, Customer[ID], Sales[ID])))

- Total Customer With Qualified Sale % = DIVIDE([Total Customer With Qualified Sale], [Total Customers])

Now it comes to my problem:

I need to cluster the groups of customers "with at least x qualified sales".

X is definied by 1, 2, 3 and 4 -> so 4 customer groups/clusters at all

next step is to have a running total of the 4 clusters

- in total

- in % of all customers

...and visualize it in two area charts with:

- X-axis = week/month, Y-axis = amount/% of customers

- one area chart show the 4 clusters in total, the other one in %

I have two slicers:

- SalesManagerID

- Customer Classification

Thank you so much for help me!

[tbones]

Thank you for the reply! 🙂

What gives me a question mark is that I didn't have the chance to try out your solution yet although it was marked as solution. Who did that and why?

[v-robertq-msft · Accepted solution]

Hi, @tbones 

According to your description and sample pictures, I can roughly understand what you want to get, you can try my steps:

1. Create a measure like this:

% of customers with at least one qualified sales =

DIVIDE(CALCULATE(DISTINCTCOUNT('Sales'[Account]),FILTER(ALLSELECTED(Sales),[Qualified_Sale]=1)),DISTINCTCOUNT('Sales'[Account]))

2. Then create a silcer and place [Owner] and a card chart to place this measure, this can achieve your first requirement:

3. Then create a calculated column like this:

Sales count =
var _count=COUNTX(FILTER(ALLSELECTED(Sales),[Account]=EARLIER(Sales[Account])&&[Qualified_Sale]=1),[ID])
return
SWITCH(
    TRUE(),
    _count>=4,"4+ sales",
    _count>=3,"3+ sales",
    _count>=2,"2+ sales",
    _count>=1,"1+ sales",
"0 sales")

And two measures like this:

Running total = COUNTX(FILTER(ALLSELECTED(Sales),[Date]<=MAX('Sales'[Date])),[ID])

% of all customers =

var _count=COUNTX(FILTER(ALLSELECTED(Sales),[Account]=MAX(Sales[Account])&&[Qualified_Sale]=1),[ID])

return

DIVIDE(_count,[Running total])

4. Then create two line chart like this, this can achieve your second requirement:

And you can get what you want.

You can download my test pbix file here

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tbones]

thank you for the reply.

It seems to be a little more complex so I created a pbix file and uploaded it here.
As I have adjusted the names a little a further explanation is need. For this I tried to visualize this here incl. An example and expected results:

hopefully this helps. Again, thank you so much for your help!

[v-robertq-msft]

Hi, @tbones 

According to your description, this problem seems to have many reasons and there seems to be no problem with your DAX statement.

Would you like to post your sample pbix file so that we can help you in advance?

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tbones]

Please drop me a message if I should provide more details. I really appreciate also partial solutions as it is urgent... Thank you so much