Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, New PBI user here.
I have a list of SITE URLs, assigned to parts of the company. I need to present where the ownership is missing. Example data to ilustrate:
Site Collection URL | Area | Owner Status |
SCOM2012 | Area 3 | |
ASAAA0065 | Area 2 | Owner Missing |
ASAAA0066 | Area 3 | |
ASAAA0073 | Area 3 | |
ASAAA0074 | Area 2 | Owner Missing |
ASAAA0074 | Area 2 | Owner Missing |
ASAAA0084 | Area 3 | |
ASAAA0084 | Area 3 | |
ASAAA0085 | Area 1 | Owner Missing |
ASAAA0085 | Area 1 | Owner Missing |
ASAAA0088 | Area 3 | |
ASAAA0088 | Area 3 |
As you can see some sites are presented twice (can have two owners missing) but I only need to count these sites as one.
I can arrive in my desired number when using clustered chart visualisation with Area as Axis, distinct count of SITE URL as value and Owner Status as filter to the value of "Owner Missing".
The issue is, I want to show this value as percentage of all sites per area so I need to calculate a measure with the same results.
What I have currently is Measure = CALCULATE ( COUNTROWS ('Sites'), 'Sites'[Owner Status] = "Owner Missing") but that simply adds +1 wherever Owner Missing is present so I somehow need to add the SIte URL check for DISTINCT COUNT but can't do it in any correct way.
Any help appreciated!
Solved! Go to Solution.
Measure = CALCULATE(DISTINCTCOUNT(Sites[Site Collection URL]), Sites[Owner Status] = "Owner Missing")
You can show a percentage if you select 'show value as-> percent of grand total' from the dropdown in the Values field well.
Measure = CALCULATE(DISTINCTCOUNT(Sites[Site Collection URL]), Sites[Owner Status] = "Owner Missing")
You can show a percentage if you select 'show value as-> percent of grand total' from the dropdown in the Values field well.
Works great, thank you Chilli
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |