Forum Discussion

seanrm42's avatar
seanrm42
Frequent Visitor
8 years ago
Solved

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 h...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] )
)
seanrm42's avatar
seanrm42
Frequent Visitor
8 years ago

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's avatar
dramus
Continued Contributor
8 years ago

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's avatar
    seanrm42
    Frequent Visitor
    8 years ago

    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's avatar
      dramus
      Continued Contributor
      8 years ago

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