topic Re: Dax Help- Average or divide by Row in DAX Commands and Tips

Dax Help- Average or divide by Row

red75116 — Wed, 14 May 2025 03:44:44 GMT

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

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!

Re: Dax Help- Average or divide by Row

Learner27 — Wed, 14 May 2025 03:57:26 GMT

Hi

Have tried with using average x and distinct ?

Thanks

Re: Dax Help- Average or divide by Row

bhanu_gautam — Wed, 14 May 2025 10:21:27 GMT

red75116 , Try using

AverageAmount =
DIVIDE(
SUM('Table'[Amount]),
COUNTROWS('Table')
)

Re: Dax Help- Average or divide by Row

red75116 — Wed, 14 May 2025 17:50:15 GMT

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 example

ID | Title | Amount
113 | Red | 14

113 | Red | 14

114 | Red | 10

114 | Red | 10
115 | Red | 20

I 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.

Re: Dax Help- Average or divide by Row

techies — Wed, 14 May 2025 18:12:29 GMT

Hi red75116 please try this

Amount =

AVERAGEX(

VALUES(Sheet7[ID]),

MAX(Sheet7[Amount])

)

Re: Dax Help- Average or divide by Row

maruthisp — Thu, 15 May 2025 05:10:23 GMT

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

Re: Dax Help- Average or divide by Row

Anonymous — Thu, 15 May 2025 05:25:30 GMT

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.

Re: Dax Help- Average or divide by Row

red75116 — Thu, 15 May 2025 13:57:23 GMT

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 | 14

113 | Red | 14

114 | Red | 10

114 | Red | 10
115 | Red | 20

I 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.

Re: Dax Help- Average or divide by Row

techies — Thu, 15 May 2025 18:04:16 GMT

Hi red75116 as i understand, please check this measure

Sum of Per-ID Averages =

SUMX(

VALUES(sheet7[ID]),

AVERAGEX(

FILTER(sheet7, sheet7[ID] = EARLIER(sheet7[ID])),

sheet7[Amount]

)

Re: Dax Help- Average or divide by Row

Anonymous — Mon, 19 May 2025 09:54:56 GMT

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.

Re: Dax Help- Average or divide by Row

red75116 — Mon, 19 May 2025 14:58:14 GMT

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!

Re: Dax Help- Average or divide by Row

Anonymous — Sun, 25 May 2025 12:46:13 GMT

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

Re: Dax Help- Average or divide by Row

Anonymous — Thu, 29 May 2025 15:09:36 GMT

Hi 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.

Re: Dax Help- Average or divide by Row

Anonymous — Mon, 02 Jun 2025 04:31:27 GMT

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.