Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @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:

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, @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:

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

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:

VahidDM_0-1629251807613.png

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

Appreciate your Kudos VahidDM_1-1629251823080.png !!

 

Anonymous
Not applicable

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 @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:

VahidDM_1-1629504743613.gif

 

 

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

Appreciate your Kudos VahidDM_0-1629504621523.png !!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.