Dax for % of task completed

[Ejykso]

Hi All,

I'll appreciate it if someone could help me with this. 

I have a table with task showing for example:
Days_Completed   Number_of_Task  Completed
Within 24 hrs       18                         18

2 days                  24                          24

3 days                  19                          19

4 days+                38                          35

Total                     99                          94

I want a dax that would show me percentage completed within each day and to be based on the total task not row by row but my dax is showing % completed for each row.  What I want is to have for example within 24 hrs = 18/99 = 18% but my dax is doing 18/18 = 100%. 
My dax is %Completed = divide([completed, [number of task],0)

Thanks

[Anonymous]

Thanks Alef_Ricardo,
The completed is a measure that calculates the completed task  and not a column in a table so there's no sum for the measure. The solution provided by v-rongtiep-msft would have worked except that the ALL in the dax is showing all total task irrespective of the date filter which is not what I'm looking for. I want the total task to be based on the date filter applied. 

[Anonymous · Accepted solution]

Hi @Ejykso ,

Please have a try

easure =
VAR _1toal =
    CALCULATE ( SUM ( 'Table'[Number_of_Task] ), ALL ( 'Table' ) )
RETURN
    MAX ( 'Table'[Number_of_Task] ) / _1toal

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks so much . This worked

[tamerj1]

Hi @Anonymous 

please try

Completed =
DIVIDE (
[completed],
CALCULATE ( [number of task], ALL ( 'Table'[Days_Completed] ) ),
0
)

[Anonymous]

Hi @Ejykso ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _1toal =
    SUMX ( ALL ( 'Table' ), 'Table'[Number_of_Task] )
RETURN
    MAX ( 'Table'[Number_of_Task] ) / _1toal

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-rongtiep-msft,

Thanks for your response above. The issue is that the ALL in the DAX is calculating for all the rows in the data. How can one make this dynmaic so that the ALL function will be based on the date selected in the slicer? for example, when I selcted a specific date, I would want the total to be used to be for that date.

Thanks

[Ejykso]

Thank you for your response. 
The issue that I have is that my number of task is already a measure ( countrows( 'data'). The days completed is a calculated column which was derived from the difference between a startdate and enddate.  So I cannot use a SUMX with an existing measure. How do workaround this?

[Alef_Ricardo_]

It seems like you want to calculate the percentage of tasks completed within each day based on the total number of tasks, rather than the number of tasks within each day. To do this, you can create a new measure that calculates the total number of tasks and use it in your %Completed measure. Here’s an example:

Total Tasks = SUM(Table[Number_of_Task])

%Completed = DIVIDE(SUM(Table[Completed]), [Total Tasks])

This should give you the percentage of tasks completed within each day based on the total number of tasks. For example, for the “Within 24 hrs” row, the calculation would be 18/99 = 18%. I hope this helps! Let me know if you have any further questions. 😊