Forum Discussion
Question on AVERAGEX
I have been using this DAX for long time, and now I would like to analyze it.
I have uploaded my PBIX file here.
Here is my DAX:
Hi JustinDoh1 ,
Thank you for reaching out to the Microsoft Community Forum.
Please check below steps for detail explaination of your DAX code with sample example.
1. SUMMARIZE ( 'Table', 'Date'[DateFormat], "ClientID_M", [ClientID_M] )
The above syntax produces a virtual table with One row per 'Date'[DateFormat]. A calculated column "ClientID_M" holding the value of your [ClientID_M] measure for that date. You are trying to generate like below sample data.
DateFormat ClientID_M
01-01-2025 12
02-01-2025 15
03-01-2025 0
2. FILTER ( … , [ClientID_M] > 0 ), It removes rows where [ClientID_M] <= 0, so you are trying to ignore zeros/negatives. Now your virtual table looks like below sample data.DateFormat ClientID_M
01-01-2025 12
02-01-2025 153. AVERAGEX ( table, [ClientID_M] ), It iterates over each row in that filtered summarized table, and evaluates [ClientID_M] for that row. It is the average of [ClientID_M] across all dates where it’s > 0.
Note: Your measure calculates the average of [ClientID_M] at the date level (one row per DateFormat), ignoring the dates where the measure is 0 or negative.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
7 Replies
What do you want to analyze? The query plan? You can use DAX Studio for that.
You would also need to investigate the code for the [ClientID_M] measure, and see if that has any issues by itself.
Hi JustinDoh1 ,
Thank you for reaching out to the Microsoft Community Forum.
Please check below steps for detail explaination of your DAX code with sample example.
1. SUMMARIZE ( 'Table', 'Date'[DateFormat], "ClientID_M", [ClientID_M] )
The above syntax produces a virtual table with One row per 'Date'[DateFormat]. A calculated column "ClientID_M" holding the value of your [ClientID_M] measure for that date. You are trying to generate like below sample data.
DateFormat ClientID_M
01-01-2025 12
02-01-2025 15
03-01-2025 0
2. FILTER ( … , [ClientID_M] > 0 ), It removes rows where [ClientID_M] <= 0, so you are trying to ignore zeros/negatives. Now your virtual table looks like below sample data.DateFormat ClientID_M
01-01-2025 12
02-01-2025 153. AVERAGEX ( table, [ClientID_M] ), It iterates over each row in that filtered summarized table, and evaluates [ClientID_M] for that row. It is the average of [ClientID_M] across all dates where it’s > 0.
Note: Your measure calculates the average of [ClientID_M] at the date level (one row per DateFormat), ignoring the dates where the measure is 0 or negative.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi JustinDoh1 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @JustinDoh1 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi JustinDoh1,
what are you asking us? you want me to explain in words what your DAX code is doing? Or ?
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
