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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.