Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |