cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BIW Frequent Visitor
Frequent Visitor

SUMX to Calculate Grand Total

Hi,

In the table (screenshot) below, the "total" where you see value of 1 is not correct.  It shall be 104,345 = 89,920 + 14,425. 

All fields belwo are "measure", except for MOQ being a "column".

 

I think inside these 2 syntaxes below, SUMX shall be used in the place where it says 1 so correct total would be shown. 

Could anybody teach how to write it?  Really appreciate!

 

QTY to Buy Suggest : this is to figure out how much stock we need to buy
=
IF(HASONEVALUE(Purchasing[Product ID Rollup]),  ROUNDUP((MAX([Days to Stock],[Days Supply])-[Days Supply])*[QTY FCST DAILY]/[Master Case],0)*[Master Case],   1    )
 
 
QTY to Buy : this is to ensure "QTY to Buy Suggest" meets supplier's requiremnet on minimum order quantity
=
IF(HASONEVALUE('Product'[Product ID Rollup]),  IF([QTY to Buy Suggest]=0,0,max([QTY to Buy Suggest],SUM(Purchasing[Vendor MOQ]))),   1   )

delete.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: SUMX to Calculate Grand Total

Hi @BIW ,

 

You can add SUMMARIZE and SUMX function in your formula, change measure like DAX below, do the same with QTY to Buy, just change the red parts of formula.

 

QTY to Buy Suggest =
var d= ROUNDUP((MAX([Days to Stock],[Days Supply])-[Days Supply])*[QTY FCST DAILY]/[Master Case],0)*[Master Case]  
var _table = SUMMARIZE(Purchasing, Purchasing[Product ID Rollup],"_Value", d)
return IF(HASONEVALUE(Purchasing[Product ID Rollup]), d, SUMX(_table,[_Value]))

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User IV
Super User IV

Re: SUMX to Calculate Grand Total

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Purchasing[Product ID Rollup]),Purchasing[Product ID Rollup],"ABCD",ROUNDUP((MAX([Days to Stock],[Days Supply])-[Days Supply])*[QTY FCST DAILY]/[Master Case],0)*[Master Case]),[ABCD])

Hope this helps.


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

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: SUMX to Calculate Grand Total

Not sure I got it correctly. Are you looking for why GT is 1?

Why you are using HASONEVALUE; IF-HASONEVALUE is one which making GT at 1.

BIW Frequent Visitor
Frequent Visitor

Re: SUMX to Calculate Grand Total

Hi,

I intentionally made it as 1 because I don't know what the syntax should be, in order to make total summing up all values in this column.  Seek for advice on this code!  Thanks,

Community Support Team
Community Support Team

Re: SUMX to Calculate Grand Total

Hi @BIW ,

 

You can add SUMMARIZE and SUMX function in your formula, change measure like DAX below, do the same with QTY to Buy, just change the red parts of formula.

 

QTY to Buy Suggest =
var d= ROUNDUP((MAX([Days to Stock],[Days Supply])-[Days Supply])*[QTY FCST DAILY]/[Master Case],0)*[Master Case]  
var _table = SUMMARIZE(Purchasing, Purchasing[Product ID Rollup],"_Value", d)
return IF(HASONEVALUE(Purchasing[Product ID Rollup]), d, SUMX(_table,[_Value]))

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User IV
Super User IV

Re: SUMX to Calculate Grand Total

Hi,

Try this measure

=SUMX(SUMMARIZE(VALUES(Purchasing[Product ID Rollup]),Purchasing[Product ID Rollup],"ABCD",ROUNDUP((MAX([Days to Stock],[Days Supply])-[Days Supply])*[QTY FCST DAILY]/[Master Case],0)*[Master Case]),[ABCD])

Hope this helps.


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

View solution in original post

BIW Frequent Visitor
Frequent Visitor

Re: SUMX to Calculate Grand Total

Thank you both so very much, Amy and Ashish!!  Can't express enough of my appreciation!!!

 

Another situation is just observed...

When I put this measure in Line chart with time in X axis, it doesn't work. 

 - Is it because measure doesn't work with chart visualization?  Do i need to create another similar measure, in order to work with time in chart? Would need some tutor on this time clause. 

 - Or how can I twist this measure to be a calculated column?

 

Thanks

Super User IV
Super User IV

Re: SUMX to Calculate Grand Total

You are welcome.  Share the link from where i can download the PBI file.  Show the issue very clearly and also show the expected result.


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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors