Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BIW
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
v-xicai
Community Support
Community Support

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

Ashish_Mathur
Super User
Super User

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

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/

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

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/
v-xicai
Community Support
Community Support

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

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.

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,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.