Forum Discussion

DainTheTrain's avatar
DainTheTrain
Regular Visitor
4 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

DainTheTrain's avatar
DainTheTrain
Regular Visitor
4 years ago

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".