Forum Discussion
power bi
Hello Power BI community,
I have got a matrix visual in a report. In that, I have created a quick measure called Duration which is the difference between created on date and completed by date. At the very last of the visual, as you know it gives out total of that duration column. I want to change it to Average. Can you please assist me with this ?
Thank you
You need to use a X iterator function like AVERAGEX
Here is some example test data
Project Start End 1 19/02/2025 20/02/2025 2 15/02/2025 20/02/2025 3 06/02/2025 16/02/2025 4 05/02/2025 09/02/2025 5 29/01/2025 07/02/2025 6 27/01/2025 05/02/2025 7 22/01/2025 29/01/2025 8 12/01/2025 22/01/2025 9 10/01/2025 19/01/2025 Here are three measures
Duration = DATEDIFF( SELECTEDVALUE('yourtable'[Start]), SELECTEDVALUE('yourtable'[End]), DAY )Total = SUMX(yourtable,[Duration])Average = AVERAGEX(yourtable,[Duration])Here is the output
Note how the X function iterates the Duration measure for each row in the table.
Please click thumbs up and accept solution if it works (it will work). Thanks 😀
Learn more here (skip the annoying adverts)
https://learn.microsoft.com/en-us/dax/averagex-function-dax
https://www.youtube.com/watch?v=UMxeti9udMo
1 Reply
speedrampsSuper User
You need to use a X iterator function like AVERAGEX
Here is some example test data
Project Start End 1 19/02/2025 20/02/2025 2 15/02/2025 20/02/2025 3 06/02/2025 16/02/2025 4 05/02/2025 09/02/2025 5 29/01/2025 07/02/2025 6 27/01/2025 05/02/2025 7 22/01/2025 29/01/2025 8 12/01/2025 22/01/2025 9 10/01/2025 19/01/2025 Here are three measures
Duration = DATEDIFF( SELECTEDVALUE('yourtable'[Start]), SELECTEDVALUE('yourtable'[End]), DAY )Total = SUMX(yourtable,[Duration])Average = AVERAGEX(yourtable,[Duration])Here is the output
Note how the X function iterates the Duration measure for each row in the table.
Please click thumbs up and accept solution if it works (it will work). Thanks 😀
Learn more here (skip the annoying adverts)
https://learn.microsoft.com/en-us/dax/averagex-function-dax
https://www.youtube.com/watch?v=UMxeti9udMo