Forum Discussion
Dax Help- Average or divide by Row
Hi red75116,
Thanks for the clarification. You are right that "AVERAGEX(VALUES(...))" ends up averaging across distinct IDs, which can lead to unexpected results if your visual still includes multiple duplicate rows. In your case, since the same ID (like 113) appears multiple times due to the join, we need a DAX measure that returns the original amount per ID, and consistently displays it across all duplicate rows.
Try this measure once:
Correct Amount :=
CALCULATE(
MAX(MergedTable[Amount]),
ALLEXCEPT(MergedTable, MergedTable[ID])
)This "MAX(MergedTable[Amount])" will get the original amount per ID (assuming the value is consistent) and "ALLEXCEPT(...)" removes any filters except for ID, so that even if the same ID appears multiple times in the visual, you’ll get the same value for each row.
This should return 14 for all rows with ID 113, 10 for all rows with ID 114 and 20 for ID 115.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team
Super Userred75116 , Try using
AverageAmount =
DIVIDE(
SUM('Table'[Amount]),
COUNTROWS('Table')
)
Hello Bhanu,
Thanks for the suggestion, but it is not providing the expected results. There any many sets of records for "red" and it is taking the count of all the red records and dividing by them instead of grouping each ID number and dividing by them. Sorry if I was not clear on the request.
Another exampleID | Title | Amount
113 | Red | 14113 | Red | 14
113 | Red | 14
114 | Red | 10
114 | Red | 10
114 | Red | 10
115 | Red | 20I would want to see 1 value or the average from the ID. Should be 44 and it is giving me the total / by all red rows (7) 13.14.
