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 III
Super User III

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
Super User II
Super User II

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors