Weighted value

[MichaelRensing]

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 like to be able to calculate a Weighted Percent Reduction with the weighting being based off of the area of the project versus the total area of all the projects in the visual, whatever filtering may be applied.  Currently I am filtering by Year, location and project type. The Sample Data is below.

Project Number	% Reduction	Project Total Area	Project type	Year	Location
14715.01	42	24166	Whole Building	2022	Louisville
14811.01	75.93	10500	Whole Building	2022	Louisville
13703.01	27.58	52579	Interior only	2022	Louisville
14604.01	44	21000	Whole Building	2022	Cincinnati
14125.01	42	106521	Whole Building	2021	Louisville

If I were Filtering by Year = 2022, Location = Lousiville, and Project Type = Whole Building the data would be:

Project Number	% Reduction	Project Total Area	Project type	Year	Location
14715.01	42	24166	Whole Building	2022	Louisville
14811.01	75.93	10500	Whole Building	2022	Louisville

I would like to have a new calculated column showing the % Weighted Reduction which should be %Reduction * (Project Total Area /  SUM(Filtered Project Total Area)) and this value uspdates based on the filters on the visual.

Thanks

[MichaelRensing]

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-C...

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 totalArea

When returning the totalArea to check, the area is the same as the row, not a Total of all the areas.

[Anonymous · Accepted solution]

Hi @MichaelRensing 

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.