Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Cheers!
Solved! Go to 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 !!
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.
Hi,
The picture says it all
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 !!
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:
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos !!
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |