Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Thanks so much . This worked
Hi @Ejy
please try
Completed =
DIVIDE (
[completed],
CALCULATE ( [number of task], ALL ( 'Table'[Days_Completed] ) ),
0
)
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.
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
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?
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. 😊
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |