Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SUMX returning wrong values for some rows

Dear All,

 

Im facing a issue while using SUMX funciton.  I Wrote the below DAX statement to calculate the Avg price using SUMX function. I found that SUMX is not working for few Rows and returning wrong values.

Could somebody chack my logic and correct me if i made any mistakes in the DAX

 

Avg_price_Gram = SUMX(MT_Monthly_TertiarySales,DIVIDE(MT_Monthly_TertiarySales[Monthly Sales Amt],MT_Monthly_TertiarySales[Monthly Sales Unit Qty]* MT_Monthly_TertiarySales[Base Pack Qty]))

 

Brand    Sale Amount        Qty           Gram     Avg price per Gm

SUMX.png

 

Thanks & Regards,

Rajeev Bikkani

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

What result do you get with this?

 

=IFERROR(MT_Monthly_TertiarySales[Monthly Sales Amt]/(MT_Monthly_TertiarySales[Monthly Sales Unit Qty]* MT_Monthly_TertiarySales[Base Pack Qty]),BLANK())

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
cthurston
Advocate II
Advocate II

Is the view you're showing us the Table view, the Data view, or is it a visual?  Can we see the field names on the image?

Anonymous
Not applicable

Hi,

 

The screen shot which im showing is table visual. My

i need to do    Avg price per gm = Monthly sales amt / (Monthly sales unit qty * Base pack qty)  for each row. But my calculation is going wrong for many rows.

 

below is my DAX formula.

 

1_MT_Avg_price_Gram = SUMX(MT_Monthly_TertiarySales,DIVIDE(MT_Monthly_TertiarySales[Monthly Sales Amt],MT_Monthly_TertiarySales[Monthly Sales Unit Qty]* MT_Monthly_TertiarySales[Base Pack Qty]))

 

If you see the below screen shot most of the rows are showing wron values. Kindly help me to fix the issue

sumx2.png

Thanks & Regards,

Rajeev

Just remove the SUMX and only use the divide function.  by using SUMX you are summing the values in the column before performing the divide.

Is what you are showing the raw data and are you using the SUMX in a calculated column or a measure?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi ,

 

My Data is coming from Azure Analysis cube and im using a Table visulization to show the data. I have  created a  Mrasure to calculate the Avg.Price per grm. If i use SUM function my totals are coming working.

 

Thanks ,

Rajeev 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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