Forum Discussion
Count the categories
I have a list with multiple rows and DCP products. I want to count how many times "Storage" alone, occurs. This is already done in a visual. I also want to count how may times the Products occur as a combination. So how many times "Storage" in this case is occuring in a combination with other products. I want it as follows in a visual in PBI. Lightblue "Storage" alone, dark blue the times "Storage" as a combination. THis will result in a row in the data to be counted multiple times, but this is also the way I want.
Thanks in advance
| AccountmanagerStringId,DCS-Products |
| 10,"Servers,Storage,Services" |
| 100,Storage |
| 108,"Servers,Storage,Network,Services" |
| 11,Storage |
| 114,Storage |
| 12,"Servers,Storage,Network,Security,Services" |
| 12,"Servers,Storage,Services" |
| 13,Storage |
| 14,"Servers,Storage" |
| 14,"Storage,Servers,Network,Security" |
| 25,"Servers,Storage,Network,Security,Services" |
| 27,"Servers,Storage,Network,Services" |
| 27,Storage |
| 28,"Servers,Storage,Services" |
| 29,"Servers,Storage" |
| 49,"Network,Servers,Storage" |
| 49,"Servers,Storage" |
| 49,"Servers,Storage,Network" |
| 49,Storage |
| 49,"Storage,Servers" |
| 50,Storage |
| 51,"Storage,Network" |
| 55,Storage |
| 57,"Servers,Storage" |
| 57,Storage |
| 60,Storage |
| 61,Storage |
| 64,"Storage,Network" |
| 66,"Servers,Storage" |
| 66,Storage |
| 7,"Servers,Network,Storage,Services,Security" |
| 7,"Servers,Storage,Network" |
| 7,"Servers,Storage,Network,Services" |
| 91,Storage |
| 94,"Servers,Storage" |
| 94,Storage |
| 97,"Servers,Storage" |
TuranK , you can create mesure for this
Storage Alone Count = COUNTROWS(FILTER('YourTableName', CONTAINSSTRING('YourTableName'[DCS-Products10], "Storage") && NOT(CONTAINSSTRING('YourTableName'[DCS-Products10], ","))))
Storage Combination Count = COUNTROWS(FILTER('YourTableName', CONTAINSSTRING('YourTableName'[DCS-Products10], "Storage") && CONTAINSSTRING('YourTableName'[DCS-Products10], ","))))
And use this measure in visual to get the output
1 Reply
- bhanu_gautam
Super User
TuranK , you can create mesure for this
Storage Alone Count = COUNTROWS(FILTER('YourTableName', CONTAINSSTRING('YourTableName'[DCS-Products10], "Storage") && NOT(CONTAINSSTRING('YourTableName'[DCS-Products10], ","))))
Storage Combination Count = COUNTROWS(FILTER('YourTableName', CONTAINSSTRING('YourTableName'[DCS-Products10], "Storage") && CONTAINSSTRING('YourTableName'[DCS-Products10], ","))))
And use this measure in visual to get the output