percentage of distinctcount

[kishoresurana]

Lets say, I have a table like this:

MetricId DeviceId Area
1 D1 Phone
1 D2 Phone
1 D3 Phone
1 D4 PC
2 D1 PC
2 D2 Phone
2 D3 Phone

When a Filter: Area = 'Phone' is applied on the report it shows the following:

MetricId UniqueDeviceCount PercentOfUniqueDeviceCount
1 3 100%
2 2 66.6%

PercentOfUniqueDeviceCount is calculated as, after the 'Area' filter is applied, divide DistinctCount of DeviceId's of the MetricId and the Area selected / DistinctCount of all DeviceId's in the Area selected.

I suspect that this going to be a DAX expression.

Thanks a ton for your help 🙂

[KGrice · Accepted solution]

Hi @kishoresurana. Based on your sample data, I was able to make this:

You don't need the middle measure on the table; it's just there to show the steps. To get there, you'll need these measures, which build on each other:

UniqueDeviceCount = DISTINCTCOUNT(TableName[DeviceId])

UniqueDeviceCount_All = CALCULATE([UniqueDeviceCount], ALLEXCEPT(TableName, TableName[Area]))

PercentOfUniqueDeviceCount = DIVIDE([UniqueDeviceCount], [UniqueDeviceCount_All])

Format the last one as a percent and you should be set.

[kishoresurana]

Hi @KGrice, thanks a lot for your reply.

I have a small complication in my actual scenario.

There are 2 tables involved (instead of 1).

Tables:

1. MetricFact table with 2 columns : MetricId, DeviceId

2. DeviceDim table contains 2 columns: DeviceId, Area

As you see, the Area column, is in a different table, called DeviceDim

Can you please alter your DAX formula to support this setup?

Again, thanks a lot!

Kishore

[KGrice]

If that's the case, how do you know which Area from DeviceDim is related to the DeviceID in the MetricFact table? For instance, your first row of data has a D1 DeviceID and an Area of Phone. If Area is in a separate table, and D1 can be either PC or Phone, how do you know which one goes on the first row?

[kishoresurana]

Hi @KGrice, thanks a lot for your reply.

I was able to figure it out, based on your reply:

UniqueDeviceCount_All = CALCULATE([UniqueDeviceCnt], ALLEXCEPT(DeviceDim, DeviceDim[Area]))

Thanks a lot!

[kishoresurana]

Lets say, I have a table like this:

MetricId DeviceId Area
1 D1 Phone
1 D2 Phone
1 D3 Phone
1 D4 PC
2 D1 PC
2 D2 Phone
2 D3 Phone

When a Filter: Area = 'Phone' is applied on the report it shows the following:

MetricId UniqueDeviceCount PercentOfUniqueDeviceCount
1 3 100%
2 2 66.6%

PercentOfUniqueDeviceCount is calculated as, after the 'Area' filter is applied, divide DistinctCount of DeviceId's of the MetricId and the Area selected / DistinctCount of all DeviceId's in the Area selected.

I suspect that this going to be a DAX expression.

Thanks a ton for your help 🙂

[KGrice]

Duplicate post?

[kishoresurana]

Lets say, I have a table like this:

MetricId DeviceId Area
1 D1 Phone
1 D2 Phone
1 D3 Phone
1 D4 PC
2 D1 PC
2 D2 Phone
2 D3 Phone

When a Filter: Area = 'Phone' is applied on the report it shows the following:

MetricId  UniqueDeviceCount   PercentOfUniqueDeviceCount

1      3        100%

2      2        66.6%

PercentOfUniqueDeviceCount is calculated as, after the 'Area' filter is applied, divide DistinctCount of DeviceId's of the MetricId and the Area selected / DistinctCount of all DeviceId's in the Area selected.

I suspect that this going to be a DAX expression.

Thanks a ton for your help 🙂

[KGrice]

Duplicate post?