cancel
Showing results for
Did you mean:
New Member

## percentage of distinctcount

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 🙂

1 ACCEPTED SOLUTION
Solution Sage

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.

8 REPLIES 8
Solution Sage

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.

New Member

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

Again, thanks a lot!

Kishore

Solution Sage

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?

New Member

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

Thanks a lot!

New Member

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 🙂

Solution Sage
New Member

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 🙂

Solution Sage

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors