Forum Discussion

tbones's avatar
tbones
Icon for Helper II rankHelper II
5 years ago
Solved

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

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:

IDEmployeeIDCustomerIDDateQualified_Sale
11234512301.01.20201
22345623410.09.20190
33456734505.07.20201

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

 

Customer table

IDCustomerIDClassificationAreaID
1123A1area1
2123A2area1
3234A1area2

CustomerID is non-unique

Classification is non-unique

AreaID is non-unique

 

Sales manager table

EmployeeIDAreaIDSalesManagerID
12345area1SM1
23456area2SM1
34567area3SM2

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!

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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]))
    1. Then create a silcer and place [Owner] and a card chart to place this measure, this can achieve your first requirement:

     

    1. 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])
    1. 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.

5 Replies

  • tbones's avatar
    tbones
    Icon for Helper II rankHelper II
    5 years ago

    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

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
    tbones
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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]))
    1. Then create a silcer and place [Owner] and a card chart to place this measure, this can achieve your first requirement:

     

    1. 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])
    1. 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's avatar
    tbones
    Icon for Helper II rankHelper II
    5 years ago

    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?