SUMX to Calculate Grand Total

[BIW]

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   )

[v-xicai · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[BIW]

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

[Ashish_Mathur]

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.

[v-xicai · Accepted solution]

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.

[amitchandak]

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]

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,