Forum Discussion
Dax Help- Average or divide by Row
I have a merged query that has a one to many (Left Join) relationship.
Data looks like
ID | Title | Amount
113 | Red | 14
113 | Red | 14
113 | Red | 14
115 | Yellow | 10
115 | Yellow | 10
120 | Green | 7
I need help with a Dax query that will Average or Divide the totals by the Count of the same ID values instead of suming all the amounts.
The table visualization should look like
113 | Red | 14. NOT 113 | Red | 42
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
13 Replies
Hi
Have tried with using average x and distinct ?
Thanksred75116 , 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.
Hi red75116,
Can you try the below DAX:
SumOfAvgPerID :=
SUMX(
VALUES( MyData[ID] ),
CALCULATE(
AVERAGE( MyData[Amount] )
)
)
OR
SumOfAvgPerID =
SUMX(
SUMMARIZE(
MyData,
MyData[ID],
"AvgAmt", AVERAGE( MyData[Amount] )
),
[AvgAmt]
)If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
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 TeamIf 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!
I was able to get the right results by creating a merged table and grouping on these values, but it would better I think if i could work this out with a measure.
Your solution is providing the average amounts for all the ID records. Instead of getting a valuue of 44, I am getting
44/3 = 14.66
ID | 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.
