Forum Discussion

MichaelRensing's avatar
MichaelRensing
Helper I
2 years ago
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 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% ReductionProject Total AreaProject typeYearLocation
14715.014224166Whole Building2022Louisville
14811.0175.9310500Whole Building2022Louisville
13703.0127.5852579Interior only2022Louisville
14604.014421000Whole Building2022Cincinnati
14125.0142106521Whole Building2021Louisville

 

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

Project Number% ReductionProject Total AreaProject typeYearLocation
14715.014224166Whole Building2022Louisville
14811.0175.9310500Whole Building2022Louisville

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • MichaelRensing's avatar
    MichaelRensing
    Helper I
    2 years ago

    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 totalArea

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.