Visualising Devices per App, Apps per Device via Histogram or Scatter

[MarksmanWaugh79]

Hi,

I have a strange problem I haven't faced before. 

We have two lists, a list of devices and the related information (hardware vendor, OS, machine name, etc) and another list list of devices and applications installed (machine name, app vendor, app name, app version, etc).

About 400 devices and about 3,000 total records in the device and applicationslist, which equals roughly 60 apps per device. 

We want to visualise this perhaps on a scatter (or histogram, or something else) that denotes how many devices have how many apps installed on them. 

Looking at the below mock up as an example of the output we're after, Y Axis is number of devices and X Axis is number of apps. Bottom left circle suggests

- 3 devices have 5 apps installed

- 35 devices have 27 apps installed

- 40 devices have 65 apps installed

- 90 devices have 67 apps installed

- etc, etc, etc

Am limited to Power Query or DAX, but I can't get my head around how I can plot X and Y axis with a measure bin, and how I would calculate in each bin how many apps or devices per bin.

Very much appreciate any assistance here. 

[parry2k]

@MarksmanWaugh one last thing, make sure to select respective reply "accept as solution"

[parry2k]

@MarksmanWaugh Glad it worked out, it is a good candidate for a YT video for my channel so that it can be repurposed. Thanks for your patience Best!

[parry2k · Accepted solution]

@MarksmanWaugh sorry about that, I thought I already gave you the solution. Here it is. Cheers!!

[MarksmanWaugh]

Confirms, brilliant, exactly what I was looking for! Thank you.

[parry2k]

@MarksmanWaugh or this, there is many way to view this data, so not sure what actually you are looking for:

This tells how many applications are installed on how many devices.

1 application installed on 2 devices.

14 applications are installed on 2 devices

15 applications are installed on 3 devices and so on.

[MarksmanWaugh]

Apologies @parry2k - is there a link to download a PBIX to verify for certain? Thanking  you again for your help, it is greatly appreciated. 

[MarksmanWaugh]

That's it! Apologies @parry2k I was still reviewing the data and comparing to your previous message when you had then already posted this. Incredible speed. This, at a glance, was exactly what I was looking for. 

This:

1 application installed on 2 devices.

14 applications are installed on 2 devices

15 applications are installed on 3 devices and so on.

was far more eloquent than I was managing in my description. 

[parry2k]

@MarksmanWaugh79 given the dataset you provided, is this the output you are expecting:

[Ashish_Mathur]

Hi,

See if my dynamic segmentation solution in the attached file helps.

[MarksmanWaugh]

Kudos to this though, it is a cool idea for other projects. 

[MarksmanWaugh]

Thank you @Ashish_Mathur, adapting this isn't quite working. Y axis needs to be number of devices, opposed to % of total. Perhaps I've made a mistake somewhere in my adaption.

I started removing sensitive information for @parry2k, I'll return to doing that and publishing to perhaps assist using that set.

[parry2k]

@MarksmanWaugh79 can you share sample pbix file, remove sensitive information before sharing. 

[MarksmanWaugh]

Hi @parry2k,

Here's a small sample of device list, and app list.

As originally posted, I've been unable to get a visual to work like the screenshot in the OP, that shows number of devices on one axis, number of apps on the other axis, and relate them such that it highlights where devices have many apps (or few apps). We're certain we could get histogram to show this or scatter with some DAX magic.

visualising-devices-per-app-and-apps-per-device.pbix (Google Drive)

Thanking you kindly. 

[Ashish_Mathur]

Hi,

Based on the PBI file that you hae shared, show the expected result very clarly in a simple Table format.

[MarksmanWaugh]

Hi @Ashish_Mathur, 

That was suggested. Customer adamant they would like a histogram or scatter to visually plot - were not happy with the table visual when shown. 

Given this is what a histogram was designed for. 

Still hoping to find a visual solution to show the grouping/bins. 

[Ashish_Mathur]

Once we get the correct numbers in the Table, we can always build a scatter plot.  I just want you to show me the correct figures in a Table format.

[MarksmanWaugh]

Hi @Ashish_Mathur 

That's one of the problems, only have DAX or Power Query as there is no ability to write queries at the source here. So somehow need to perform the counts via some sort of DAX Summary table, or something. 

So list of devices and the count of apps for each device. Then List of apps and count of devices for each app. Then somehow relate them and plot.