Forum Discussion
Word in Column count: unique and percentage
Hello Power BI masters!
I have a table "All devices", in which I have columns with device names bu users like this:
| User name | Device model |
| Alex | Samsung A3 |
| Helene | Samsung A1 |
| Jorge | Apple 13 |
| Olga | Apple 13 |
| Robert | Nokia 3310 |
My goal is to understand % of each model in device park like:
Samsung A3 - 20%
Samsung A1 - 20%
Apple13 - 40%
Nokia 3310 - 20%
Could you please help with correct measure syntax?
I understand that it's easier to have a static list of possible models, but it's not my way because it changes constantly...
*I also have another table that shows me the number of specific applicaton crashes on each user device.
Further I would need to compare "% of crashes by device model/all crash cases" with "% of device model/whole device models park"...
Hence, I'm looking for advice how to calculate % of models in park better so I could use the result then in next actions.
Could you please advice?
3 Replies
- m_dekorte
Resident Rockstar
Hi Khramana,
You can leverage a Table.RowCount and Table.Group for that.
Copy this code into a new blank query
let TotalRows = Table.RowCount( Source ), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJrVDSUQpOzC0uzUtXcDRWitWJVvJIzUnNS0UWNwSLe+UXpYOEHQsKclIVDCGK/XPSE9HFgvKTUotKgKJ++dmZiQrGxoYGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User name" = _t, #"Device model" = _t]), GroupPerc = Table.Group(Source, {"Device model"}, {{"Count", each Table.RowCount(_)/TotalRows, Percentage.Type}}) in GroupPercFirst I added a TotalRows = Table.RowCount( Source ),
Then you can use Group By on the ribbon, as key set Device Model
And for the aggregation select Count Rows, update the code in the formula bar.
Table.Group(Source, {"Device model"}, {{"Count", each Table.RowCount(_) /TotalRows, Percentage.Type}})
With this result
Ps. If this helps solve your query please mark this post as Solution, thanks!
