Forum Discussion
Consolidated Count
Hello fellow PBIX users.
I need your help for an executive request that I know is simple but has me stumped.
I have a large table which has a DeviceID and the AppID. For each App the Device has installed there is a row.
Example:
Device 1 App1
Device 2 App1
Device 2 App2
Device 2 App3
Device 3 App2
I need a summarized view that lookcs like this:
Count of Apps Count of Device
1 2
3 1
Showing the number of devices which have have a certain counts of the apps. We want to start with the devices which have the most apps installed and work our way back.
Try this calculated Table
New Table = SUMMARIZE ( SUMMARIZE ( TableName, TableName[Devices], "Count of Apps", COUNT ( TableName[Apps] ) ), [Count of Apps], "Count of devices", COUNT ( TableName[Devices] ) )
10 Replies
I would do it with two summary tables.
Devices = SUMMARIZE('Devices and Apps',[Device],"# of Apps",count('Devices and Apps'[App]))Apps = SUMMARIZE('Devices and Apps',[App],"# of Devices",count('Devices and Apps'[Device]))You can then join these to the main data table.
I created the two tables but am having a struggle getting to the finalized summary view my executive is looking for.
He wants to know how many devices have 1 app, how many devices have 2 apps, how many devices have 3 apps. and so on. The maximum number is 17 and I've compiled the data in excel wiht a pivot table just want it to refresh automatically in PBI from the data source directly.
Given this data:
You want this result:
To get this I took the [# of Apps] (not summarized) and the count distinct [Device] from the 'Devices' table.
SummarizedTable = SUMMARIZE( Table1 ; Table1[DeviceID]; "#ofDev" ; DISTINCTCOUNT( Table1[DeviceID] ) ; "#ofApps" ; COUNTROWS( Table1 ) )Then create a group from "#ofApps" and size of the bin is just 1
Which of the new tables (Apps or Devices) does 'Table 1' represent in your formula?
When I created your formula I got 3 columns instead of 1.
- Table 1 is exactly like your example and you should have 3 columns
