cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## SUMX vs SUM gives different total - why

Hi,

I am learning X-Functions in PowerBI. I have table (visitData) in which there are 4 customer and their total number of visit and their spent per visit.

Customer, SpendPerVisit,No.OfVisit
a,50,7
b,40,3
c,100,12
d,15,4

Now, i am using measure to calculate their total spent per visit and AverageSpentPerVisit without using X-functions

AverageSpentPerVisit Wrong = AVERAGE(VisitData[spend per visit])
TotalSpentPerVisit = [AverageSpentPerVisit Wrong] * [TotalNoOfVisit]
it gives me result as below:-

When i use X-Function and calculate Average = Divide(totalSpent,NoOfVisit) then it gives different result as shown in below :-
Measures :
AverageSpentPerVisit New = DIVIDE([TotalSpentSumX],[TotalNo.OfVisit New])

1 ACCEPTED SOLUTION
Frequent Visitor

Hi @affan,

But i got work around the solution and i check the matrix total is right. Why, because

in this case, in the Total column is like :-

TotalOfTotalSpendPerVisit/TotalOfTotalNo.OfVisit

That's Why it is showing me \$51 in first matrix and \$66 in second matrix.

4 REPLIES 4
Solution Sage

If you see both of your total results you can understand that both of them are wrong.

In AverageSpentPerVisit Wrong the total is shown as 51.25.

In AverageSpentPerVisit New the total is shown as 66.54

Whereas the total should be 205. Your issue is not with the SUM or SUMX it is regarding the 'Row Context'

AverageSpentPerVisit Wrong = var _avg=AVERAGE(VisitData[spend per visit])
return
IF(HASONEVALUE(VisitData[Customer]),_avg,SUMX(VALUES(VisitData[Customer]),_avg))

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

Frequent Visitor

Hi @affan,

But i got work around the solution and i check the matrix total is right. Why, because

in this case, in the Total column is like :-

TotalOfTotalSpendPerVisit/TotalOfTotalNo.OfVisit

That's Why it is showing me \$51 in first matrix and \$66 in second matrix.

Community Support

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors