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.
I have a table that looks like this:
The end result would need to look like this:
The table result will change based on other filters selected, so I don't think it can be a calculated column. A measure makes more sense, but have struggled trying to figure out how to sum the data dynamically based on filters selected. Any assistance is appreciated! Thank you.
Unless I am missing something, you should just be able to do something like:
Total Cost = SUM([Cost]) Total Quantity = SUM([Quantity]) Cost Per = DIVIDE([Total Cost],[Total Quantity],BLANK())
Thank you for the suggestion. I tried it and it gave the same results as my calculation below, but both are still not quite the right answer. It needs to be the summation of all costs / the summation of all quantities averaged over all locations depending on the filter selected.
Value =
var __Numerator = CALCULATE(sum(Table[Cost]),FILTER(Table, Table[Location]=SELECTEDVALUE(Table[Location])))
var __Denominator = CALCULATE(sum(Table[Quantity]),FILTER(Table, Table[Location]=SELECTEDVALUE(Table[Location])))
return DIVIDE(__Numerator/__Denominator)
[Total Cost] = sum ( 'Table'[Cost] ) [Total Quantity] = sum ( 'Table'[Quantity] ) [Average Cost] = divide ( [Total Cost], [Total Quantity] ) [Your Measure] = averagex( addcolumns( summarize( 'Table', 'Table'[Location] ), "'Table'[AvgCostPerLocation]", [Average Cost] ), 'Table'[AvgCostPerLocation] )
Please never use the table name in front of a measure and always use the table name before a column. This is one of the most important things in good DAX. Never deviate from this. EVER.
Best
Darek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |