cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chitemerere
Responsive Resident
Responsive Resident

% Calculation from a Column with Mixed Values

I have the following dataset from a table called "Quality Facts":
 

Analysis Outcome

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Does Not Comply

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

Complies

 
I am trying to calculate the Complying % as follows:
Complying % =
VAR Complying =
CALCULATE(DISTINCTCOUNT('Quality Facts'[Sample No#]), 'Quality Facts'[Analysis Outcome] = "COMPLIES")
VAR AllComplying =
CALCULATE([Number of Complying Products], ALL('Quality Facts'))
 
RETURN
DIVIDE(Complying, AllComplying)
 
"Number of Complying Products" = CALCULATE(DISTINCTCOUNT('Quality Facts'[Sample No#]), 'Quality Facts'[Analysis Outcome] = "COMPLIES"
 
The above gives the following results, whic are wrong:
 
Complying %.PNG

 

 
Kindly assist with the right measure.
 
Regards,
Chris
6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xulin-mstf
Community Support
Community Support

Hi @Chitemerere,

 

I’m not clear your data structure.

What condition value does ‘AllComplying’ return?

It returns 100% when [Sample No#] are Non-duplicate values in your measure.

Try measure as:

Complying % =

var Complying =

CALCULATE(

    COUNTROWS('Quality Facts'),

FILTER(

    ALL('Quality Facts'),

    'Quality Facts'[Analysis Outcome]="Complies"

)

)



var Allcomplying=

CALCULATE(

    COUNTROWS('Quality Facts'),

    ALL('Quality Facts')

)



RETURN

Complying/Allcomplying

Here is the output:

v-xulin-mstf_0-1611738085945.png

 

Here is the demo, please try it: % Calculation from a Column with Mixed Values

 

Best Regards,

Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thany ou very much but that is not what i am looking for

Hi @Chitemerere,

 

I’m not clear your data structure.

What condition value does ‘AllComplying’ return?

It returns 100% when [Sample No#] are Non-duplicate values in your measure.

Could provide more datails?

 

Best Regards,

Link

nandic
Memorable Member
Memorable Member

Hi @Chitemerere ,
Try changing variable for Complying by adding filter part:
CALCULATE (
    DISTINCTCOUNT ( 'Quality Facts'[Sample No#] ),
    Filter('Quality Facts', 'Quality Facts'[Analysis Outcome] = "COMPLIES")
)

Regards,
Nemanja Andic

Chitemerere
Responsive Resident
Responsive Resident

Nemanja

 

Thank you very much for your input.  I have tried your suggestion and it does change the end results, it gives the same output as my original measure.

 

Regards,

Chris

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors