cancel
Showing results for
Did you mean:
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:

Kindly assist with the right measure.

Regards,
Chris
6 REPLIES 6
Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
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:

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

Best Regards,

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

Responsive Resident

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

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.

Could provide more datails?

Best Regards,

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

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

Announcements