Dynamic groups of customers based on usages data in columns

[Anonymous]

Hi All,

I have an excel sheet of application usage analytics structured as follows:

Each row represents a customer (customer ID is column A)

Column B onwards - each column represents a piece of functionality in the system.  If the column has a 0 customer has not used this functionality, if 1 present customer has.

Id like to be able to group similar patterns of usages into same buckets 

e.g. sum of all cutsomers who have used no functionality (so all 0s in every column) would form initiative 1

All customers with 1 in column b and c would form inititaive B for example

So basically, each initiative would be a count of customers based on the same pattern of 0s and 1s in column b onwards 

[lbendlin · Accepted solution]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@lbendlin and @Ashish_Mathur quick q - how did you transpose my original data (which had functionality as columns) into the functionality as repeating rows for each customer ID?  Did you do it manually because the data sample was small?  Or is there an automatic way of doing that? Thanks in advance

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, I used the "Unpivot Other columns" feature.

[lbendlin]

That is by far the most frequently used transformation in Power Query, especially when the data source is Excel. Power Query needs simple tabular format without any fancy grouping or pivoting.

[Ashish_Mathur]

Completely agree.

[Anonymous]

Wow thank you both for the reply - I will take a look through both PBIX.  Many Thanks

[Anonymous]

Thank you for the advice.  Below is al ink with sample data and sample structure and a little background into the need.  Appreciate it!

https://docs.google.com/spreadsheets/d/1pr9dHXEuUhLCjLufQfGryIp9Bpm9xiUb/edit?usp=sharing&ouid=11405...

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin]

"All customers with 1 in column b and c would form inititaive B for example"

You need to be more specific.  What are your bucket definitions? Or do you expect the user to make choices on the activities slicer and then the report will calculate the number of customers falling into that pattern?

Here is my interpretation:

Customers = if(ISFILTERED(Attributes[Activity]),
var r=CALCULATETABLE('Table',TREATAS(FILTERS(Attributes[Activity]),'Table'[Attribute]))
var u = SUMMARIZE(r,'Table'[Customer ID],"smu",sum('Table'[Value]))
var v = FILTER(u,[smu]=COUNTROWS(FILTERS(Attributes[Activity])))
return 0+countrows(v),
var s = SUMMARIZE('Table','Table'[Customer ID],"sm",sum('Table'[Value]))
var t = FILTER(s,[sm]=0)
return 0+countrows(t))

[lbendlin · Accepted solution]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523