cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gonzaloalvarezg
Helper II
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

Hi @gonzaloalvarezg 

 

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:

VahidDM_1-1629504743613.gif

 

 

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

Appreciate your Kudos VahidDM_0-1629504621523.png !!

View solution in original post

6 REPLIES 6
v-angzheng-msft
Community Support
Community Support

Hi, @gonzaloalvarezg 

 

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:

vangzhengmsft_0-1629428898885.png

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

Hi,

The picture says it all

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Community Champion
Community Champion

Hi @gonzaloalvarezg 

 

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:

VahidDM_0-1629251807613.png

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

Appreciate your Kudos VahidDM_1-1629251823080.png !!

 

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

Hi @gonzaloalvarezg 

 

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:

VahidDM_1-1629504743613.gif

 

 

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

Appreciate your Kudos VahidDM_0-1629504621523.png !!

View solution in original post

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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.