Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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.
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?
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!
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 🙂
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |