Represent count across another count

[sam01454]

Hi, I have a table that has the account id and the device id associated with the account. One account can have multiple devices. I want to represent how many accounts have 1 devices, how many accounts has 2 devices and so on. I am not able to represent the count(account) across count(device) in a bar graph in direct query mode. The sql will look something like this: select A.dvc, count(A.account) (select account, count(devices) as dvc from table) A Thanks

[v-cherch-msft]

Hi @sam01454 

You may drag the account id and device id columns as below when creating the bar graph.

Regards,

[sam01454]

This didn't work, it is just giving two count, the device count is not coming in the x axis.

[v-cherch-msft]

Hi @sam01454 

If you want to get the count and use it in x-axis.You may get the count columns and use them.I would suggest you create a new table with SUMMARIZE function like below.Then drag the count columns to x-axis.

Table2 =
SUMMARIZE (
    'Table',
    'Table'[account id],
    'Table'[device id],
    "Count1", COUNT ( 'Table'[account id] ),
    "Count2", COUNT ( 'Table'[device id] )
)

For further,please refer to below article.

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

For 3rd party Links:
Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Regards,

[v-cherch-msft]

Hi @sam01454 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,