Frequency of counts

[ddtblar]

hi guys,

Need your help. I am lost.

i have data just like this:

NO_ID

L4147RG

L1211PR

M5526PA

L6737QC

S4968KN

L4147RG

S4968KN

S4968KN

M5526PA

L4147RG

L4147RG

So, basically, i am trying to count  NO_ID in the table based on group of frequency (1; 2; 3 - 5; 6-8).

Example, when  L4147RG counted 4 times, it will go to "3-5 times" category,

When L1211PR counted 1 time, it will go to "1 times" category,

The total will sum how many no_id that goes to each category.

so the result that i want is just like this:

frequency	total no_id
1 times	2
2 times	1
3-5 times	2
6-8 times	0
Total	10

Any hints? 

any help would be great.

thanks

(sorry for the bad english)

[Zubair_Muhammad · Accepted solution]

@ddtblar

Try this calculated column

Column =
VAR R =
    CALCULATE ( COUNT ( Table1[NO_ID] ), ALLEXCEPT ( Table1, Table1[NO_ID] ) )
RETURN
    SWITCH (
        TRUE (),
        R = 1, "1 times",
        R = 2, "2 times",
        R < 6, "3-5 times",
        R < 9, "6-8 times"
    )

[Zubair_Muhammad · Accepted solution]

@ddtblar

Try this calculated column

Column =
VAR R =
    CALCULATE ( COUNT ( Table1[NO_ID] ), ALLEXCEPT ( Table1, Table1[NO_ID] ) )
RETURN
    SWITCH (
        TRUE (),
        R = 1, "1 times",
        R = 2, "2 times",
        R < 6, "3-5 times",
        R < 9, "6-8 times"
    )

[ddtblar]

Wow thanks for your fast response and answer! It really solve my problem. 

[Zubair_Muhammad · Accepted solution]

@ddtblar

If you want to select one year at a time....you can adjust the calculated column as follows

Please see attached file

Column = 
VAR R =
    CALCULATE ( COUNT ( Table1[NO_ID] ), ALLEXCEPT ( Table1, Table1[NO_ID] ,Table1[datetime].[Year]) )
RETURN
    SWITCH (
        TRUE (),
        R = 1, "1 times",
        R = 2, "2 times",
        R < 6, "3-5 times",
        R < 9, "6-8 times"
    )

[ddtblar]

hey zubair, i get the idea of the calculated column which counted it per year based on this formula : 

Column = 
VAR R =
    CALCULATE ( COUNT ( Table1[NO_ID] ), ALLEXCEPT ( Table1, Table1[NO_ID] ,Table1[datetime].[Year]) )
RETURN
    SWITCH (
        TRUE (),
        R = 1, "1 times",
        R = 2, "2 times",
        R < 6, "3-5 times",
        R < 9, "6-8 times"
    )

Now i want to change the formula so it just counted per month, so change the formula to this:

Column = 
VAR R =
    CALCULATE ( COUNT ( Table1[NO_ID] ), ALLEXCEPT ( Table1, Table1[NO_ID] ,Table1[datetime].[Month]) )
RETURN
    SWITCH (
        TRUE (),
        R = 1, "1 times",
        R = 2, "2 times",
        R < 6, "3-5 times",
        R < 9, "6-8 times"
    )

But it counted not like i want. This formula count the ID in all month no matter what year it is. So the result of this formula is:

L1601HS	01/01/2016	3 times
L1601HS	05/01/2016	3 times
L1601HS	05/02/2017	2 times
L1601HS	16/01/2018	3 times
L1601HS	05/02/2018	2 times

what i really want for the result is :

L1601HS	01/01/2016	2 times
L1601HS	05/01/2018	2 times
L1601HS	05/02/2017	1 times
L1601HS	16/01/2018	1 times
L1601HS	05/02/2018	1 times

So it count just in that month and just that year. Is that possible? or the formula need to be changed? any help would be great.

Thanks!

[Zubair_Muhammad]

@ddtblar

Then we can use this column as Row Field and Distinct Count of IDs as Value

Please see attached file

[ddtblar]

hey zubair, I want to ask again,

how if i want to make the frequency based on date?

I want to know how many it counts per month in a year. So when i change the date slicer, it will show different result.

example :

i choose the date slicer in 2017

so the table (1 times, 2 times, etc) will show the data just in 2017.

can you help about this scenario?

thanks

[Zubair_Muhammad]

@ddtblar

Then we will need a MEASURE

If you can copy paste some data with expected results, I will try to write a MEASURE for you

[ddtblar]

this is example of my data table :

NO_ID	datetime
L9436NI	20/12/2016
L9436NI	21/12/2016
L9436NI	05/02/2017
L9436NI	15/02/2017
L9436NI	14/04/2016
L9436NI	19/04/2016
L9436NI	01/02/2018
L9436NI	20/11/2016
W8836XG	26/02/2016
W8836XG	02/02/2018
W8836XG	26/04/2017
W8836XG	26/05/2017
W8836XG	13/02/2018
L1601HS	21/12/2016
L1601HS	30/01/2018
L1601HS	05/02/2017
L1601HS	31/03/2018

So in the report screen, i want to make a table just like you already made before, but it can change based on the date slicer.

this is what i expected for the result:

slicer :	2016
frequency	total_noid
1 times	2
2 times	0
3-5 times	1
6-8 times	0

when i change the slicer to the different periode, the total_noid should changed too.

slicer :	2017
frequency	total_noid
1 times	1
2 times	2
3-5 times	0
6-8 times	0

slicer :	2018
frequency	total_noid
1 times	2
2 times	1
3-5 times	0
6-8 times	0

It count the id that meet the condition of the slicer.

I hope you can help me with this scenario, thanks zubair.