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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.