cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Patryk Frequent Visitor
Frequent Visitor

DISTINCT COUNTROW based on value

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 URLAreaOwner Status
SCOM2012Area 3 
ASAAA0065Area 2Owner Missing
ASAAA0066Area 3 
ASAAA0073Area 3 
ASAAA0074Area 2Owner Missing
ASAAA0074Area 2Owner Missing
ASAAA0084Area 3 
ASAAA0084Area 3 
ASAAA0085Area 1Owner Missing
ASAAA0085Area 1Owner Missing
ASAAA0088Area 3 
ASAAA0088Area 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!

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: DISTINCT COUNTROW based on value

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.

2 REPLIES 2
HotChilli New Contributor
New Contributor

Re: DISTINCT COUNTROW based on value

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.

Patryk Frequent Visitor
Frequent Visitor

Re: DISTINCT COUNTROW based on value

Works great, thank you Chilli

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 273 members 2,667 guests
Please welcome our newest community members: