Create Dynamic Ranges classification for Sales

[PauloRegado]

Hello, after watching a lot of tutorials and post about ranges but most of them only apply on fixed values that won't changes or a single point of data i'm at the point that i can't think of any way other than fake the report with bookmark which is a solution that i would want to avoid.

I have a table called "AgenteVS" that contains the information of the employes, "Receita" that contains data about sales, "Intervalo" Cobrados" that has the name of the intervals and the minimum value and maximum and you can also see the table "Datas" and "Periodos" that is used to filter the sales by date intervals ( 12 months, 6 months, 3 months and 1 month)

the table "Intervalo Cobrados" should have data like this

Intervalo	Min	Max
No Sales	0	1
1 to 10 sales	1	11
10 to 50	11	51
More than 50	51

what i'm trying to achieve is to classify each employe using the sales data and i've been able to do it with a calculated column first to create a table like this which is what i was trying to achieve:

Intervals	Nº Employes
No Sales	5
1 to 10 sales	10
10 to 50	6
More than 50	2

The problem appaeared when i was asked to add a way to filter the sales data and the Nº of employes for each intervals should update but in my case since i added the intervals as a column to the table AgenteVS the values stayed the same when i filtered the sales. I tried to use a measure but i can't use it as the first column of the table for the intervals.

Then i tried to just create a relationship to the table "Intervalo Cobrados" then create measure to count the employes there but i can't since the table a no relationship with the table employes and i can't seem to be able to create one since the values have no point i can join.

Is there a way to create this or do i really have to create a column that count the sales of the employes in for each filter (12 months, 6 months, ect..) ?

Thank you for your time and if there is anything where i didn't explain well enough i can elaborate on it.

[v-yohua-msft]

Hi, @PauloRegado 

You need a measure that calculates sales per employee for the selected time interval.

Sales Count = COUNTROWS('Receita')

Based on the Sales Count measure, create additional measures to classify employees into intervals

Classification = 
VAR SalesCount = [Sales Count]
RETURN
SWITCH(
    TRUE(),
    SalesCount = 0, "No Sales",
    SalesCount >= 1 && SalesCount <= 10, "1 to 10 sales",
    SalesCount >= 11 && SalesCount <= 50, "10 to 50 sales",
    SalesCount > 50, "More than 50",
    "Unclassified"
)

To show the number of employees in each classification, you can use the matrix visual. Place the Classification measure in the Rows area, and use another measure to count the employees in the Values area.

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[PauloRegado]

Sorry for the delay i ended up going the long way and creating columns for each classification.

Relativelly to your response i can't seem to be able to use measure in the Rows for the Matrixes to try you solution.