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)
Solved! Go to Solution.
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" )
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 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" )
Then we can use this column as Row Field and Distinct Count of IDs as Value
Please see attached file
Wow thanks for your fast response and answer! It really solve my problem.
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
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
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.
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" )
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!
We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.
Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!
User | Count |
---|---|
151 | |
84 | |
77 | |
46 | |
44 |