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,
Thanks for reaching out to the Microsoft fabric community forum.
This is a common issue that occurs when working with merged queries involving one-to-many relationships.
From what you’ve described, your table is showing multiple rows for the same ID due to a left join with another table that has multiple matches per ID. Because of this, when you look at the Amount field in your visual, it’s getting summed across all the duplicated rows which is why you're seeing inflated totals (like 14 * 3 = 42 for ID 113).
Even though each instance of Amount is 14, it's repeated three times in the joined data, so Power BI is doing exactly what it's told summing all visible values.
To display just the original amount per ID, you'll need to use a DAX measure that removes the duplication effect. Here’s one way to do it:
Correct Amount :=
AVERAGEX(
VALUES(MergedTable[ID]),
CALCULATE(MAX(MergedTable[Amount]))
)
You can add this measure to your visual instead of the regular Amount field, and it should return the correct result per ID like 14 for ID 113 instead of 42.
I am also attaching my PBIX file. You can go through it and check if it works according to your requirement.
I would also take a moment to thank techies, bhanu_gautam and Learner27, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference
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
If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
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 Team
