cancel
Showing results for
Did you mean:  Helper II

## Percentaje of compliance

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!

1 ACCEPTED SOLUTION  Community Champion

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: Appreciate your Kudos !!

6 REPLIES 6  Community Support

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.  Super User

Hi,

The picture says it all Regards,
Ashish Mathur
http://www.ashishmathur.com  Community Champion

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: Appreciate your Kudos !!  Helper II

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?.  Community Champion

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: Appreciate your Kudos !!  Helper II

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!   