Forum Discussion
How to Summarize Values Based on Count
I have a data source with a list of ID's and a Type:
| ID | Type |
| 111 | A |
| 222 | A |
| 333 | A |
| 444 | B |
| 555 | B |
| 666 | C |
| 777 | D |
| 888 | E |
| 999 | F |
| 000 | G |
I created a table visual in Power BI where I counted the instances of ID for each Type:
| COUNT(ID) | Type |
| 3 | A |
| 2 | B |
| 1 | C |
| 1 | D |
| 1 | E |
| 1 | F |
| 1 | G |
The next step I want to take is find the decimal of the COUNT(ID) against the grand total, and any decimal value that is below .2, will be combined into a type defined as "Other".
So that means this table:
| COUNT(ID) | Type |
| 3 | A |
| 2 | B |
| 1 | C |
| 1 | D |
| 1 | E |
| 1 | F |
| 1 | G |
Would then turn into:
| COUNT(ID) | Type |
| 3 | A |
| 2 | B |
| 5 | Other |
Because types C through G would have a value of .1, and thus would be combined into the defined type "Other".
Please and thank you for your help!
Hi DainTheTrain ,
Here are the steps you can follow：
1. Create calculated column.
Type_Column = var _count=COUNTX(FILTER(ALL('Table'),'Table'[Type]=EARLIER('Table'[Type])),[ID]) var _countall=COUNTROWS('Table') var _value= DIVIDE( _count,_countall) return IF( _value<0.2,"Other",[Type])2. Place [Type_Column] and [ID]. and set [ID] – Count.
3. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
DainTheTrain have you looked at the Binning and Grouping features of Power BI?
littlemojopuppy Yes I have, but the native function of binning and grouping doesn't work for my needs. Binning doesn't help with categorical values, such in my case, the Type. I am grouping together the Type based on the value of COUNT(ID).
Sure, I could use the grouping function and manually select the types that need to be grouped, but that isn't very intuitive or efficient. I want a conditional grouping based on the decimal (%) value of COUNT(ID) against the grand total.
E.g. Type A has 3 occurances, so the COUNT(ID) value will be 3. There are 10 rows, meaning 10 total occurances. So Type A would have a value of 30%, or 0.30.
Type C has 1 occurance. So COUNT(ID) is 1. There are 10 total occurances in the table. So Type C would have a value of 10%, or 0.10. Based on my condition, if this value is below 0.20, it needs to be grouped into "Other".
Hi DainTheTrain ,
Here are the steps you can follow：
1. Create calculated column.
Type_Column = var _count=COUNTX(FILTER(ALL('Table'),'Table'[Type]=EARLIER('Table'[Type])),[ID]) var _countall=COUNTROWS('Table') var _value= DIVIDE( _count,_countall) return IF( _value<0.2,"Other",[Type])2. Place [Type_Column] and [ID]. and set [ID] – Count.
3. Result:
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
