Consolidated Count

[seanrm42]

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.

[Yggdrasill]

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

[seanrm42]

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.

[Yggdrasill]

Table 1 is exactly like your example and you should have 3 columns

[Zubair_Muhammad · Accepted solution]

@seanrm42

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] )
)

[Zubair_Muhammad]

@seanrm42

[dramus]

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.

[seanrm42]

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.

[dramus]

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.

[seanrm42]

I think I am missing this step:

"You can then join these to the main data table."

Is this a union in DAX or a merge in the tables and if so which tables merging on what?

Sorry for all the questions but I greatly appreciate the help.

[dramus]

"You can then join these to the main data table."

Is this a union in DAX or a merge in the tables and if so which tables merging on what?

I mean create relationships between your original table and the two new summary tables.