Incorrect column totals average

[kumsha1]

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 now the average column is directly coming from DB...TIA.

[PhilipTreacy · Accepted solution]

Hi @kumsha1 

Download example PBIX file.

Use this measure

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

Regards

Phil

[PhilipTreacy]

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]

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.

DailyValue	DailyPlanValue	Value/Plan
92018	74639.36	123.28
115946	105329.98	110.08
147581	112604.98	131.06
136989	134429.98	101.90
134605	145263.39	92.66
80192	129910.83	61.73
104279	114558.27	91.03
811610	816736.78	99.37	101.68