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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors