Group with count with new category

[bak7]

Hello,

i have the following issue.

i have data like below and i want to group them like this:

1) How many times it appears  each one

2) group them like this: 

0-2 : 2 shops

3-4:3 shops

for example

VF158 appears 4 times so it should be grouped as 3-4

Each one as count. I tried to create a table

VF777

VF398

VF158

VF158

VF158

VF158

VF461

VF401

VF405

[v-lionel-msft · Accepted solution]

Hi @bak7 ,

You can do like this.

Column = 
VAR x = 
CALCULATE(
    COUNT(Sheet2[Data]),
    ALLEXCEPT( Sheet2, Sheet2[Data])
)
RETURN
SWITCH(
    TRUE(),
    x>=0 && x<=2 , "0-2",
    x>=3 && x<=4, "3-4",
    ">=5"
) 

Best regards,
Lionel Chen

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

[bak7]

Something i did not mentioned before is that i want to filter it per month.

i have data from 2018 , so for a specific shop which has 300 sales the last 2 years i want to filter it and say that for april 2020 it has 17 sales so it belongs to category 11-20 and not 100+. So, the chart will change after every filter selection.

is that possible in BI?

[bak7]

Hello,

Thank you so much for the answer it was really helpful.

Now i have a different issue. 

The data i have are for the sales of shops.

For April for the 200 shops, the 150 had sales so the 50 of them had zero sales.

I have created a mapping with a unique code for each shop and in the current column you helped me with i want to make a vloukup with the set of 200 shops.

For example

shop	category
VF345	0
VF777	2-4
VF398	5-10

i cant depict the zero values for April and every month i want to show

this is the final function for the new column

Category =

VAR x =

CALCULATE(

COUNT(Financial[Fran Εισαγωγής]);

ALLEXCEPT( Financial;Financial[Fran Εισαγωγής])

)

RETURN

SWITCH(

TRUE();

x=0 ; "0";

x>0 && x<=10; "1-10";

x>=11 && x<=20; "11-20";

x>=21 && x<=30; "21-30";

x>=31 && x<=50; "31-50";

x>=51 && x<=100; "51-100";

x>=101 ; "100+")

[amitchandak]

@bak7 , Refer Segmentation
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/

[bak7]

thanks for the answer.

However i though a different approach.

i used this in order to create a new column and then have my new categorization but i got this message :"Token EOF expected". i guess this is not the right way to write it properly.

any suggestion?

if[Count]={0} then "0" else[Count]={1-10} then "1-10"
else[Count]={11-20} then "11-20" else[Count]={21-30} then"21-30" else[Count]={31-40} then"31-50" else[Count]={51-100} then "51-100" else "100+"