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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
ChrisW3
Frequent Visitor

Measure to Sum Values of Another Measure Aggregated is a Summary Table

I have a model based on inventory issue transactions. I have one measure that calculates a monthly average of items issued from inventory. I have another measure that does that same thing, but filters based on a second calendar table I created with an inactive relationship to the inventory transactions table so I can have 2 date slicers to select 2 different date periods to compare the monthly averages between 2 periods. I also have another measure that calculates an average item price, and I want to take the difference in monthly average quantities multiplied by the average price to get a total $ variance. Where I'm having trouble is getting that calculated total $ variance to total up the way I want it. I want it to calculate the $ variance per inventory item number, but then sum the values of that measure rather than use the same calculation across all the items in aggregate. Here are my measures:

 

Total Issues = SUM([QTY_IN_EACH])

Number of Months = DISTINCTCOUNT(Issues_All[YearMonth])

Monthly Average = [Total Issues]/[Number of Months]

Monthly Average - Comparison Period = CALCULATE([Monthly Average],ALL('Calendar'),USERELATIONSHIP(Issues_All[I-Date],'Calendar Reference'[Date-Comparison]))

Change from Comparison Period = [Monthly Average]-[Monthly Average - Comparison Period]

Average Unit Cost = SUMX(Issues_All,[UNIT_PRICE_PER_EACH]*[QTY_IN_EACH])/SUMX(Issues_All,[QTY_IN_EACH])

Change from Comparison Period - Extended per Average Unit Cost = [Change from Comparison Period]*[Average Unit Cost]

 

How do I write a measure that will calculate that last measure per [ITEM_NO], then take those values for each item and them sum them? This image is what I currently have, and I want the Card that currently shows 90,242.99 to show $40,865.76, which is the sum of the values displayed in the last column of the table (the last DAX measure in the list I provided above).

 

Current draft visualCurrent draft visual

2 REPLIES 2
AllisonKennedy
Super User
Super User

Is your Average Unit Cost Total what you expect? Using a SUMX in that last measure may be the key to your success.

See if this helps any:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

I do think SUMX is the way to go, however I've tried 2 different variations and neither is giving desired result:

 

SUMX ( VALUES ( Issues_All[ITEM_NO] ), [Change from Comparison Period - Extended per Average Unit Cost] )


SUMX(SUMMARIZE(Issues_All[Item No],"Total Difference",[Change from Comparison Period - Extended per Average Unit Cost]),[Total Difference])

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.