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.
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
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |