cancel
Showing results for
Did you mean:
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
=

2 ACCEPTED SOLUTIONS

Accepted Solutions
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]

Best Regards,

Amy

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

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
6 REPLIES 6
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.

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

## 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]

Best Regards,

Amy

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

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

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

Announcements