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
Hi red75116,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Hi these solutions provided are still not returning the expected results. The workaround is not a DAX solution, but actually merging queries and grouping by the ID and Title.
I still be would be interested in geetting the Dax correct so that I could get this working without having to create another table. Thanks!
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 TeamHi red75116,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.Hi red75116,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.