cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Advocate II
Advocate II

Re: SUMX returning wrong values for some rows

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?

Highlighted
Helper I
Helper I

Re: SUMX returning wrong values for some rows

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

Highlighted
Super User IX
Super User IX

Re: SUMX returning wrong values for some rows

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate II
Advocate II

Re: SUMX returning wrong values for some rows

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.

Highlighted
Helper I
Helper I

Re: SUMX returning wrong values for some rows

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 

Highlighted
Super User V
Super User V

Re: SUMX returning wrong values for some rows

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/

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors