Forum Discussion

kumsha1's avatar
kumsha1
Post Patron
4 years ago
Solved

Incorrect column totals average

Hi,   In the table visual below, the row wise average seems ok but the column totals average is incorrect. Can someone let me know what might have gone wrong here and help to rewrite the DAX, as of...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi kumsha1 

     

    Download example PBIX file.

     

    Use this measure

    AvgX = AVERAGEX('DataTable', DIVIDE(SUM('DataTable'[DailyValue]),SUM('DataTable'[DailyPlanValue]))) * 100

     

     

    Regards

     

    Phil

PhilipTreacy's avatar
PhilipTreacy
Super User
4 years ago

Hi kumsha1 

 

So the last column is showing averages?  How is this calculated?  It seesm to be showing (for the most part) the ratio of Daily Value / DAILY_PLAN_VALUE * 100

 

Please supply your data, it's hard to work things out with just a summary of the data and not knowing how the calculations are being made.

 

But, if the Average column is coming from the database, I can't change those values.  The column will need to be recomputed in DAX.

 

I suspecty the issue is something to do with the average being calcalated using AVERAGE rather than using AVERAGEX over the table.

 

Regards

 

Phil

  • kumsha1's avatar
    kumsha1
    Post Patron
    4 years ago

    Hi PhilipTreacy 

    Average is coming from the DB, i need the average to DailyValue TOTAL/DailyPlanValue TOTAL, not the average of the average column...Below is the data.

     

    DailyValueDailyPlanValueValue/Plan 
    9201874639.36123.28 
    115946105329.98110.08 
    147581112604.98131.06 
    136989134429.98101.90 
    134605145263.3992.66 
    80192129910.8361.73 
    104279114558.2791.03 
    811610816736.7899.37101.68