Classification from dynamic data

[PauloRegado]

Hello,

I have a project where i have to classify the employes based on the sales but i have to be able to filter the products and the classification has to change acordingly.

Lets say i have this table for the classification:

Interval	Min	Max
Tier S	100
Tier A	60	99
Tier B	20	59
Tier C	0	19

And some data from sales:

Employe	Product	Sales
John	Paint	20
Emily	Paper	10
John	Paper	30
Jack	Paper	10
Jack	Paint	50
John	Paint	30
Rose	Paper	80
Rose	Paint	20

What i'm trying to achieve would be a table like this:

ClassificationNumber of Employes

Interval	Number of Employes
Tier S	1
Tier A	2
Tier B	0
Tier C	1

And ideally be able to have a matrix that has a subrow level that let me see which employe belong to the Interval.

I need to be able to use a slicer to chose between the product Paint or paper or any other product that may appear and the classification of the employes should be recalculated using the product selected or the whole of his sales is none is selected.

I already tried to use a calculated column in a table for the employes but since the calculated column only update when the dataset is refreshed the slicer don't change the classification.

If i tried to use a measure with a switch to make a dax version of the table intervals i can't use the measure as the first column of my matrix.

Here is the link for the PBI files with the example data already loaded since i can't give the real data.

Example 

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[PauloRegado · Accepted solution]

Thanks for the answer but the examples that you gave never let me see which customer belong to which category with a subrow for example here:

I tried to see if the choosing a ctaegory the customer list updated but there is not changes in the results for example the category "2k - 5k" has 639986,56 sales but the customer list remains with the full total:

I was trying to achieve something like this

If i can't find a way to make it work i may need to go with your solution but i don't know if the client will accept

[Ashish_Mathur]

It seems to work fine.

[PauloRegado]

Sorry i was using the wrong field, its exactly what i needed, i don't know if i will work with the other requirements but your example will greatly help me to at least have a starting point.

[Ashish_Mathur]

You are welcome.

[Ritaf1983]

Hi @PauloRegado 
This is a scenario of dynamic segmentation.

Please check the linked video:

https://www.youtube.com/watch?v=FDa0I3J3h-c

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