Dax Help- Average or divide by Row

[red75116]

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!

[techies]

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]

    )

)

[red75116]

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

113   | Red         |  14

114   | Red         |  10

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.

[v-mdharahman]

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.

[v-mdharahman]

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.

[red75116]

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!

[v-mdharahman · Accepted solution]

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

[v-mdharahman]

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.

[v-mdharahman]

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.

[maruthisp]

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

[techies]

Hi @red75116 please try this

 Amount =

AVERAGEX(

    VALUES(Sheet7[ID]),

    MAX(Sheet7[Amount])

)

[bhanu_gautam]

@red75116 , Try using

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

[red75116]

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

113   | Red         |  14

114   | Red         |  10

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.

[Learner27]

Hi 

Have tried with using average x and distinct ?

Thanks