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

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.