cancel
Showing results for
Did you mean:
Regular Visitor

Frequency of counts

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

Re: Frequency of counts

@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"
)

Try my new Power BI game Cross the River
Super User

Re: Frequency of counts

@ddtblar

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

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"
)
Try my new Power BI game Cross the River
8 REPLIES 8
Super User

Re: Frequency of counts

@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"
)

Try my new Power BI game Cross the River
Super User

Re: Frequency of counts

@ddtblar

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

Try my new Power BI game Cross the River
Regular Visitor

Re: Frequency of counts

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

Regular Visitor

Re: Frequency of counts

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.

thanks

Super User

Re: Frequency of counts

@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

Try my new Power BI game Cross the River
Regular Visitor

Re: Frequency of counts

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.

Super User

Re: Frequency of counts

@ddtblar

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

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"
)
Try my new Power BI game Cross the River
Regular Visitor

Re: Frequency of counts

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!

Announcements