Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
11 months ago
Solved

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:   Averagex = AVERAGEX(         FILTER             (       ...
"DAX" "Need help"
  • v-dineshya's avatar
    v-dineshya
    11 months ago

    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    15

     

    3. 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

v-dineshya's avatar
v-dineshya
Community Support
11 months ago

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    15

 

3. 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

v-dineshya's avatar
v-dineshya
Community Support
11 months ago

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

  • v-dineshya's avatar
    v-dineshya
    Community Support
    11 months ago

    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

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    11 months ago

    v-dineshya Actually, somehow, my post was published it, intending to delete after I figured out by myself. Sorry about that. But, I was trying to break down each piece and understand where the <expression> is for that AVERAGEX code. Thank you.