cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User

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
amitchandak Super Contributor
Super Contributor

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
Super User

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
Super User

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 138 members 1,741 guests
Please welcome our newest community members: