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'm having trouble producing a DAX calculation that basically aggregates a column value at a higher dimension level than the row level of the query. Because I'm connecting to a SSAS Tabular model, I kinda have to do this as a measure. The screenshot below shows my table with project and task costs broken down by month. I basically want to create a measure that takes each Project Materials Cost (one-time) and sums it exactly once per project so that it will equal $925,000 ($500k + 250k +$175) rather than summing the field value for every row as displayed below. I also want to be able to use a slicer to filter projects and have the measure aggregate the Project Materials Cost (one-time) value correctly. How could I do this without changing the table structure?
Solved! Go to Solution.
Here is one way to do it
NewMeasure =
SUMX (
VALUES ( Table[Project] ),
CALCULATE ( AVERAGE ( Table[Project Materials Cost] ) )
)
Replace Table with your actual table name.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@TimQ - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Here is one way to do it
NewMeasure =
SUMX (
VALUES ( Table[Project] ),
CALCULATE ( AVERAGE ( Table[Project Materials Cost] ) )
)
Replace Table with your actual table name.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |