Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Using AVERAGEX ON SUMMARIZE DATA

Hi,

 

I am relatively new to PowerBI and DAX and trying to build a sales report. and would greatly appreciate some help with trying to get an AVERAGEX formula to work.

 

I am trying to calculate the monthly average sales by location by product using the summarize function. This works fine. Then when I apply the AVERAGEX it does not work.

 

Here is my SUMMARIZE formula

 

SALES BY LOCATION =
    SUMMARIZE(Sheet1,Sheet1[Item],Sheet1[LOCATION],Sheet1[Date].[Month],"Total Qty",SUM(Sheet1[QTY]))
 
Here is the table that is created
 

 

 

I am trying to calculate the average monthly  sales quantity for each item by location based on the values in the above table.

 

 

So for item K110 at location A the average sales qty should be (3+2+25)/3 (3 rows in summary table) = 10

 

 

Here is the formula I am using that gives me an error

 

SALES BY LOCATION =
 AVERAGEX(
    SUMMARIZE(Sheet1,Sheet1[Item],Sheet1[LOCATION],Sheet1[Date].[Month],"Total Qty",SUM(Sheet1[QTY])),[Total Qty])

 

 
What I am expecting is a new column to the right of Total Qty in the Sales by Location table with the same average qty - for all K110 items . i.e Average qty column for K110 for location A would have a value of 10 for a January, February and March row.
 
Am I expecting a result that is not possible.
 
Thanks for you assistane in advance.
Tips and Tricks

13 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Anonymous  - You're on the right track, but the issue arises because AVERAGEX does not recognize [Total Qty] as a valid column within SUMMARIZE. 

     

    SALES BY LOCATION =
    VAR SalesSummary =
    SUMMARIZE(
    Sheet1,
    Sheet1[Item],
    Sheet1[LOCATION],
    Sheet1[Date].[Month],
    "Total Qty", SUM(Sheet1[QTY])
    )
    RETURN
    ADDCOLUMNS(
    SalesSummary,
    "Avg Monthly Qty",
    CALCULATE(AVERAGEX(SalesSummary, [Total Qty]),
    ALLEXCEPT(SalesSummary, Sheet1[Item], Sheet1[LOCATION]))
    )

     

    This should give you a column with the same average monthly quantity for all months of a given location and item.

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi rajendraongole1


      Thanks for your assitance with this. I tried to use your formula and understand what it is trying to achieve. However I am getting this error message when using ALLEXCEPT

       

       

      It's as if the VAR SalesSummary isn't recognised as a table that is expected with ALLEXCEPT

       

      Here is the dataset I am using:

       

      LOCATIONItemQTYSALESDatemonth
      AK110220001/02/20252
      BK110550001/01/20251
      AK110331005/01/20251
      AK11015150008/03/20253
      BK1102400001/02/20252
      BK11010100009/02/20252
      BK110550007/03/20253
      BK200433506/02/20252
      AK200433504/02/20252
      BK200864006/03/20253
      AK11010120012/03/20253
      AK200648715/02/20252
      BK200757228/02/20252

       

       

      Thank you for you looking into this.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Just to be clear. I would expect the average qty of item K110 at location A to be 10 (2+3+15+10)/ 3 ( this data covers 3 months)

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Why are you creating a calculated table formula?  Why are you not writing measures in a visual?  Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Ashish, 

       

      It makes sense to use measure if I don't need a new table

       

      How would I do that ?

       

      Below is the data

       

      LOCATIONItemQTYSALESDatemonth
      AK110220001/02/20252
      BK110550001/01/20251
      AK110331005/01/20251
      AK11015150008/03/20253
      BK1102400001/02/20252
      BK11010100009/02/20252
      BK110550007/03/20253
      BK200433506/02/20252
      AK200433504/02/20252
      BK200864006/03/20253
      AK11010120012/03/20253
      AK200648715/02/20252
      BK200757228/02/20252
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Also here is an example of an expected result. I would expect the average qty of item K110 at location A to be 10 (2+3+15+10)/ 3 (data spans 3 months)

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

     

    Try this:

        GROUPBY (
       YourTableExpression,
        "New column name", AVERAGEX ( CURRENTGROUP (), [column to average within the table expression] )
  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    Hello Anonymous ,

     

    Please try this.

     

    SALES BY LOCATION =
    AVERAGEX(
    SUMMARIZE(
    Sheet1,
    Sheet1[Item],
    Sheet1[LOCATION],
    FORMAT(Sheet1[Date], "MMM YYYY"), -- Convert Date to Month-Year format
    "Total Qty", SUM(Sheet1[QTY])
    ),
    [Total Qty]
    )

     

    Thanks,
    Pankaj

    If this solution helps, please accept it and give a kudos, it would be greatly appreciated.