Measure that counts multiple values and shows as percentage

[Anonymous]

Hi,

Im looking to create a measure to show a percentage of appointments that have either "ATTENDED EARLY" or "ATTENDED IN TARGET".

The field has the following appointment outcomes "ATTENDED EARLY", "ATTENDED IN TARGET", "ATTENDED LATE" and  "APPOINTMENT NOT ATTENDED"

I have tried the following measure but it isnt working so im expecting im missing something. 

Measure = DIVIDE(

CALCULATE(COUNTX(FACT_Appt,FACT_Appt[APPOINTMENT SUMMARY] IN {"APPOINTMENT ATTENDED EARLY","APPOINTMENT ATTENDED IN TARGET"})),

CALCULATE(COUNTX(FACT_Appt,FACT_Appt[APPOINTMENT SUMMARY] IN {"APPOINTMENT ATTENDED EARLY","APPOINTMENT ATTENDED IN TARGET","APPOINTMENT ATTENDED IN TARGET","APPOINTMENT ATTENDED LATE","APPOINTMENT NOT ATTENDED"}))

)

Any help would be apprechiated

Thanks

[Mariusz]

Hi @Anonymous 

Try this 

Measure = 
DIVIDE(
    CALCULATE(
        COUNTROWS( FACT_Appt ), 
        FACT_Appt[APPOINTMENT SUMMARY] IN { "APPOINTMENT ATTENDED EARLY", "APPOINTMENT ATTENDED IN TARGET"}
    ),
    CALCULATE(
        COUNTROWS( FACT_Appt ), 
        ALL( FACT_Appt )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi,

Thanks for your help.

I tried the measure but added some specific criteria as there are 2 values i want to exclude. I want to exclude "NO APPOINTMENT" and "FUTURE APPOINTMENT". When i try this and place the measure into a Visualisation it shows 0

Measure = DIVIDE( CALCULATE( COUNTROWS( FACT_Appt ), FACT_Appt[APPOINTMENT SUMMARY] IN { "APPOINTMENT ATTENDED EARLY", "APPOINTMENT ATTENDED IN TARGET"} ), CALCULATE( COUNTROWS( FACT_Appt ), ALL( FACT_Appt ) ) )

Any ideas on why this is 0

Thanks

[Mariusz]

Hi @Anonymous 

You can exclude by using NOT IN{ "value1",  "Value2" } ingstead of IN 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thanks

When i tried the NOT IN it returned a syntax error. Also when i tried your original measure that showed as a 0 aswell

This returned an error under the NOT IN function

Measure = DIVIDE( CALCULATE( COUNTROWS( FACT_Appt ), FACT_Appt[APPOINTMENT SUMMARY] IN { "APPOINTMENT ATTENDED EARLY", "APPOINTMENT ATTENDED IN TARGET"} ), CALCULATE( COUNTROWS( FACT_Appt ), FACT_Appt[APPOINTMENT SUMMARY] NOT IN {"APPOINTMENT IN FUTURE","NO CURRENT APPT WINDOW"} ) )

[Mariusz · Accepted solution]

Hi @Anonymous 

Sorry, my bad NOT needs to go before column as in the example below.

No idea why you showing 0 as I can't see your data, can only guess, but you can:

1. format the measure to %
2. create a data sample so I can apply the calculation to and test.

Measure 9 = 
DIVIDE( 
    CALCULATE(
        COUNTROWS( 'Product' ),
        'Product'[Color] IN { "Azure" }
    ), 
    CALCULATE(
        COUNTROWS( 'Product' ),
        NOT 'Product'[Color] IN { "Blue" }
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn