How to show number of customers per "Sales Group"

[tsoulge]

Hello,

I have to create a visual, showing in the y axis the different (ideally dynamically created) groups/bins of sales and the in x axis, the number of (unique) customers that belong to the respective group as per the selections of the user (months, countries, products) An example of the final visual is this screenshot below:

I am facing 2 main issues and I would like your support:

1. Since I have created the intervals (groups of sales) in my Fact table (where I have my measure: Sales), with the addition of a new column and using the switch function, unfortunately I show only the customers with at least 1 sale, meaning I exclude all customers with no sales.  As it makes sense of course, my Fact table doesn't show all customers and I need also to show customers with 0 sales. (I have tried to use my "Customers" table, but no success so far, since it always shows all customers allocated to each group).
2. The most important issue is I cannot show only 1 customer per product/period. My DAX formula for my measure is the following:

# Customers =
VAR LastMonth = DATESMTD(DATEADD('Date'[Date],-1,MONTH))
VAR Customers = CALCULATE(DISTINCTCOUNT(Fact[Customer_ID]), 'Date'[Date] > LastMonth)
Return Customers

So this creates an issue, as per period/product there will be only one group assigned to every Customer_ID, but I want to show the total value and then to assign it to the respective group of sales. This is what I get when for example I select only ONE customer and 2 products and 2 periods with different values:

I have tried to use group by/summarize to take the total Sales by product/period but again I don't get the correct outcome.

I am definitely missing something but still cannot find it...  Any feedback would be greatly appreciated.

Thanks!

[tamerj1 · Accepted solution]

Hi @tsoulge 

manually create a table (Groups) that contains

[Group Name] (0 Sales, 3 Sales, 5 - 10 Sales etc..),

[Index] (For sorting), 

[Lower Limit] (0, 3, 5, etc)

[Higher Limit] (0, 3, 10, etc)

Place Groups[Group Name] in the table visual along with the following measure

# Customers =
COUNTROWS (
    FILTER (
        CROSSJOIN ( Groups, VALUES ( Customer[Customer_ID] ) ),
        VAR NumOfSales =
            COUNTROWS ( CALCULATETABLE ( Fact ) )
        RETURN
            NumOfSales >= Groups[Lower Limit]
                && NumOfSales <= Groups[Higher Limit]
    )
)

[tsoulge]

Hi @tamerj1  many thanks for your quick reply.

I used your solution, and I created the Groups table and the measure "# Customers" as suggested :

but the outcome again is not what I want, since it shows all customers assigned to all groups:

Any idea what is missing? Thanks!

[tamerj1]

@tsoulge 

Just noticed the mistake. I have fixed the code in the original post. 
however, ot seems that you missed to conver the data type of the two limt columns to whole number. 

 

[tsoulge]

Hi @tamerj1, thanks again for your response.

I converted the data type of limit columns and I updated the DAX as suggested and now the output is very close to the solution but unfortunately still not 100% correct after the validation I did.

The good thing is that now I am able to show all customers (including these with 0 sales - so that already solves my 1st issue) but the customers are not properly allocated to groups:

For example, if I select one customer, I see that is assigned to one group only, which is fine, but the group is not the correct one. Please see below screenshot:

The total Sales are 42 but the customer belongs to group "6-10 sales", while it should be in "31-50 sales" (I haven't done any selection, meaning I show all products & all months). I see we don't use the the Sales measure in the DAX formula, shall we add it to change the logic based on how we create the groups?

FYI the group names have been manually added to "Group" table, using Power Query and "Enter Data", since it's a new table and I cannot use the switch function with my Sales measure, as I used in my previous implementation in the Fact table. Is this the correct way? Thanks again!

[tamerj1]

@tsoulge 
I cannot think of anything other than the data type. Would you please double check (again) from the query editor that the both low and high limits are of whole number data type.

Who said you cannot use SWITCH?! However, FILTER is by far the most simple solution.

[tsoulge]

Hi @tamerj1, I accepted your solution, since I found the issue and I fixed it. I had to change the expression of "NumOfSales" variable, since I want to get the Sum of Sales and not the count of rows, but he rest is fine and now it works perfectly.

Thanks again!