Forum Discussion
Solved
Weighted value
I have a set of data that has various projects, the project level % Reduction, and the projects total area. There are other data point attached to the projects such as location and year. I would li...
You can try the following code
Measure = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Sheet1' ), [Project type] IN VALUES ( 'Sheet1'[Project type] ) && [Year] IN VALUES ( 'Sheet1'[Year] ) && [Location] IN VALUES ( 'Sheet1'[Location] ) ), [Project Total Area] ) RETURN DIVIDE ( SUM ( 'Sheet1'[Project Total Area] ) * SUM ( 'Sheet1'[% Reduction] ), a )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I found this post, and am trying to work with it. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Weighted-Average-Based-on-Multiple-Critera-and-Filters/m-p/1156248
The issue I am facing is that I cannot get the Area to sum. I have made a new Measure
Measure =
VAR selectedYear = SELECTEDVALUE(Sheet1[Year])
VAR location = SELECTEDVALUE(Sheet1[Location])
VAR reduction = SELECTEDVALUE(Sheet1[Percent reduction])*SELECTEDVALUE(Sheet1[Total area])
VAR totalArea = CALCULATE(SUM(Sheet1[Total area]),Filter(Sheet1, Sheet1[Year]=selectedYear && Sheet1[Location]=location))
VAR weightedReduction = DIVIDE(reduction,totalArea)
RETURN totalAreaWhen returning the totalArea to check, the area is the same as the row, not a Total of all the areas.
You can try the following code
Measure =
VAR a =
SUMX (
FILTER (
ALLSELECTED ( 'Sheet1' ),
[Project type]
IN VALUES ( 'Sheet1'[Project type] )
&& [Year]
IN VALUES ( 'Sheet1'[Year] )
&& [Location] IN VALUES ( 'Sheet1'[Location] )
),
[Project Total Area]
)
RETURN
DIVIDE (
SUM ( 'Sheet1'[Project Total Area] ) * SUM ( 'Sheet1'[% Reduction] ),
a
)
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.