Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kishoresurana
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
KGrice
Memorable Member
Memorable Member

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

 

MetricID.PNG

 

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.

View solution in original post

8 REPLIES 8
KGrice
Memorable Member
Memorable Member

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

 

MetricID.PNG

 

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.

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

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?

 

TableStruc.PNG

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
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 🙂

kishoresurana
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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.