cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramandeep007 Frequent Visitor
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:-
Error.PNG
 
 
 
 
 
 
 
 
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])
 
correct.PNG
 
 
 
 
 
Can someone please help me to defferentiate the result between this?
Thanks in advance
 
1 ACCEPTED SOLUTION

Accepted Solutions
ramandeep007 Frequent Visitor
Frequent Visitor

Re: SUMX vs SUM gives different total - why

Hi @affan,

 

Thanks for reply and helpin me. your solution is also working.

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.

 

 

View solution in original post

4 REPLIES 4
themistoklis New Contributor
New Contributor

Re: SUMX vs SUM gives different total - why

affan Established Member
Established Member

Re: SUMX vs SUM gives different total - why

Hi @ramandeep007

 

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

ramandeep007 Frequent Visitor
Frequent Visitor

Re: SUMX vs SUM gives different total - why

Hi @affan,

 

Thanks for reply and helpin me. your solution is also working.

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.

 

 

View solution in original post

Community Support Team
Community Support Team

Re: SUMX vs SUM gives different total - why

@ramandeep007,

 

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)