Percentaje of compliance

[Anonymous]

Hi,

I need to put on a card the percentage of compliance of certain status that I have only one column on an Excel Database. In other words I need to divide the Finish by the total (Finished plus cancelled) without creating a new table just using a Dax Formula.

Does anyone knows what this DAX Formula will be?.

I have attached the exact column for the Dax formula; this is next to various other columns

https://gofile.io/d/fl4zD5

Cheers!

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a measure like this:

% of Closed =
VAR _total =
    CALCULATE ( COUNT ( 'Table'[Status] ), ALL ( 'Table' ) )
VAR _finished =
    CALCULATE (
        COUNT ( 'Table'[Status] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Status] = "Closed" )
    )
RETURN
    DIVIDE ( _finished, _total )

If you want to dynamically calculate the percentage based on the results filtered by the slicer, then replace ALL with ALLSELECTED.

% of Closed =
//var _total=CALCULATE(COUNT('Table'[Status]),ALL('Table'))
//var _finished=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Closed"))
//base on slicer,such as date, you could uses ALLSELECTED like below
VAR _total =
    CALCULATE ( COUNT ( 'Table'[Status] ), ALLSELECTED ( 'Table' ) )
VAR _finished =
    CALCULATE (
        COUNT ( 'Table'[Status] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Status] = "Closed" )
    )
RETURN
    DIVIDE ( _finished, _total )

result:

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

The picture says it all

[VahidDM]

Hi @Anonymous 

In your file, there is not any Finished status [let me know if I'm wrong], but if you want to find the % of Closed status, try the below measure:

% of Closed =
VAR _CountClosed =
    CALCULATE ( COUNTA ( Hoja1[Status] ), Hoja1[Status] = "Closed" )
RETURN
    VAR _CountTotal =
        CALCULATE ( COUNT ( Hoja1[Status] ), ALL ( Hoja1 ) )
    RETURN
        _CountClosed / _CountTotal

The output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[Anonymous]

Hi VahidDM!,

Well I modified the DAX formula and it worked flawlessly.

The thing is that the percentage should be 100% if they are all closed.

What should I add to the DAX formula to accomplish this?.

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

% of Closed =
VAR _CountClosed =
    CALCULATE ( COUNTROWS ( Hoja1 ), FILTER ( Hoja1, Hoja1[Status] = "Closed" ) )
RETURN
    VAR _CountTotal =
        COUNTROWS ( Hoja1 )
    RETURN
        VAR _ClosePercent = _CountClosed / _CountTotal
        RETURN
            IF ( ISBLANK ( _ClosePercent ), 0, _ClosePercent )

It shows 100% when they are all closed and 0% when they are all Open:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[Anonymous]

Aweosome! it worked!.

I just added a column to the Status file which is "Programmed Date".

Next to the percentage of compliance I figured it would be usefull to use a "Expected compliance index" which will be calculated as follows. 

For example:

Today is the 23th of August 2021 and we have 100 lines which all have a programmed date.

But only 10 have a date of 22-08-2021, so the percentage of compliance will be 10%, because only ten lines of a 100 have a date inferior to 23th of August which are supposed to be done by the 23-08-2021.

So the calculation will be 10 (the amount of lines with a date under 23th of August) divided by 100 (amount of lines).

How can I create this DAX formula?.

Cheers!