The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 visual
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])
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
12 | |
8 | |
8 |