Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.