Forum Discussion

LYeo87's avatar
LYeo87
Helper II
1 year ago
Solved

% result errors using dax

Hello,

 

Im trying to gain a % of audit items when the audit item result is 'complaint' - it works fine when i use the full data set. But as soon as I use a slicer it through the figures.

Dax used is:

% = [6) Count of Audit Item ID for Compliant]/[2) AUDIT ITEMS DISTINCT COUNT NO RESULT FILTER)]
6) Count of Audit Item ID for Compliant =
CALCULATE(
    DISTINCTCOUNT('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item ID]),
    'Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item Result]
        IN { "Compliant" }
)

2) AUDIT ITEMS DISTINCT COUNT NO RESULT FILTER) = CALCULATE([1) AUDIT ITEMS ID DISTINCT)],REMOVEFILTERS('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item Result]))

Any advice?

 

Correct:


Incorrect when slicing:

 





Need Help
Show and Tell
Tips and Tricks
  • wardy912's avatar
    wardy912
    1 year ago

    Hi LYeo87 

     

     It looks like you're only removing filters from the audit item result column, so other slicers will be changing the result. Try this:

     

    % Compliant = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item ID]),
        'Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item Result] = "Compliant"
    ),
    DISTINCTCOUNT('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item ID])
)

     

     I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

4 Replies

  • wardy912's avatar
    wardy912
    Super User
    1 year ago

    Hi LYeo87 

     

     It looks like you're only removing filters from the audit item result column, so other slicers will be changing the result. Try this:

     

    % Compliant = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item ID]),
        'Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item Result] = "Compliant"
    ),
    DISTINCTCOUNT('Dynamics - Pbi HSE_Audit_Inspections_LC'[Audit Item ID])
)

     

     I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

  • v-echaithra's avatar
    v-echaithra
    Community Support
    11 months ago

    Hi @LYeo87 ,

    Thank you wardy912  for your inputs.
    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Thank you for your patience and look forward to hearing from you.

    Best Regards,
    Chaithra E.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    11 months ago

    Hi LYeo87 ,

    I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

    Thank you.

  • v-echaithra's avatar
    v-echaithra
    Community Support
    11 months ago

    Hi LYeo87 ,

    May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

    Thank you.