Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tsoulge
Helper I
Helper I

How to show number of customers per "Sales Group"

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:# Customers.png

 

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:# Customers selected.png

 

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!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

 

 

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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

 

 

Hi @tamerj1  many thanks for your quick reply.

 

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

Groups table.png

 

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

# Customers new table.png

 

Any idea what is missing? Thanks!

@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. 

CCF1D803-8134-4C28-8DE1-25AE6A1BD3F5.jpeg

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:

# Customers new table 2.png

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:

# Customers new table correct.png

 

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!

@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.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.