cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ddtblar Regular Visitor
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:

frequencytotal no_id
1 times                          2
2 times                          1
 3-5 times                          2
 6-8 times0
Total                       10

 

 

Any hints? 

any help would be great.

 

thanks

(sorry for the bad english)

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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

View solution in original post

Super User
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

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

View solution in original post

8 REPLIES 8
Super User
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

View solution in original post

Super User
Super User

Re: Frequency of counts

@ddtblar

 

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

Please see attached file

 

fre.png

 

Try my new Power BI game Cross the River
ddtblar Regular Visitor
Regular Visitor

Re: Frequency of counts

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

 

 

ddtblar Regular Visitor
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.

 

can you help about this scenario?

thanks

 

Super User
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
ddtblar Regular Visitor
Regular Visitor

Re: Frequency of counts

this is example of my data table :

 

NO_IDdatetime
L9436NI20/12/2016
L9436NI21/12/2016
L9436NI05/02/2017
L9436NI15/02/2017
L9436NI14/04/2016
L9436NI19/04/2016
L9436NI01/02/2018
L9436NI20/11/2016
W8836XG26/02/2016
W8836XG02/02/2018
W8836XG26/04/2017
W8836XG26/05/2017
W8836XG13/02/2018
L1601HS21/12/2016
L1601HS30/01/2018
L1601HS05/02/2017
L1601HS31/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
  
frequencytotal_noid
1 times2
2 times0
3-5 times1
6-8 times0

 

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

 

slicer :2017
  
frequencytotal_noid
1 times1
2 times2
3-5 times0
6-8 times0

 

 

slicer :2018
  
frequencytotal_noid
1 times2
2 times1
3-5 times0
6-8 times0

 

  

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

 

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

 

 

 

 

 

 

Super User
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

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

View solution in original post

ddtblar Regular Visitor
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:

L1601HS01/01/20163 times
L1601HS05/01/20163 times
L1601HS05/02/20172 times
L1601HS16/01/20183 times
L1601HS05/02/20182 times


what i really want for the result is :

L1601HS01/01/20162 times
L1601HS05/01/20182 times
L1601HS05/02/20171 times
L1601HS16/01/20181 times
L1601HS05/02/20181 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!

 

 

 

 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)