Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Filter-weighted average

Good afternoon, please if someone can help me with a measure that can filter the result by weighting the Indicator with respect to the existing amount per Year / Center / Winery since in some I repeat the winery with different indicator.

YearAreaCenterWine cellarQuantityIndicator
2018EleventhRabudos10148.0291,98
2018EleventhRabudos10242.9473,60
2018EleventhRabudos10213.0802,40
2018EleventhRabudos10350.8001,98
2018EleventhRabudos10449.3003,64
2018EleventhRabudos10550.8002,42
2018EleventhRabudos10649.8563,64
2018EleventhRabudos10750.8002,13
2018EleventhRabudos10851.7441,50
2018EleventhRabudos10950.8002,13
2018EleventhRabudos11022.5561,61
2018EleventhRabudos11028.2441,50
2018EleventhRabudos11150.8001,50
2018EleventhRabudos11251.0611,43
2018EleventhRabudos11350.8001,50
2018EleventhRabudos11425.8301,25
2018EleventhRabudos11424.3831,61
2018EleventhRabudos11550.8293,00
2018EleventhRabudos11650.9441,50
2018EleventhRabudos11750.8103,30
2018EleventhRabudos11850.5871,65
2020EleventhRabudos10151.7043,84
2020EleventhRabudos10250.8002,45
2020EleventhRabudos10351.6873,37
2020EleventhRabudos10450.8003,70
2020EleventhRabudos10551.4003,08
2020EleventhRabudos10641.3001,21
2020EleventhRabudos1064.0002,60
2020EleventhRabudos1065.5002,86
2020EleventhRabudos10750.8003,81
2020EleventhRabudos10850.8003,38
2020EleventhRabudos10950.8003,36
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    Sorry for the late reply. You can create a measure with below DAX to get the weighted average indicator. 

    New Indicator = 
VAR _table =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SELECTCOLUMNS (
                'Table',
                "Quantity", 'Table'[Quantity],
                "Indicator", 'Table'[Indicator]
            ),
            "Percentage of quantity", DIVIDE ( [Quantity], SUM ( [Quantity] ) )
        ),
        "Apportionment", [Percentage of quantity] * [Indicator]
    )
RETURN
    SUMX ( _table, [Apportionment] )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    Thank you for providing the sample data. It is very helpful. However currently I don't understand what the expected result should be like. Can you explain how to weight and filter with some examples? For example, in 2018, Wine cellar 102 is repeated, how do you want to deal with it? 

     

    Best Regards,
    Community Support Team _ Jing

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago
      Thank you for the help lcreo to solve it in this way:
      Indicator = SUMX('tabla1',('Bi Sowing'[Quantity])*('table1' [indicator]))/sum('table1' [Quantity])
      I have filter by year and center on the sheet, the idea is that for cases that are repeated the indicator is representative according to the center / winery according to the amount that each indicator has
      YearCenterWine cellarQuantity IndicatorPercentage of quantityApportionment
      2018Rabudos102429473,677%2,75954808
      2018Rabudos102130802,423%0,56030128
      56027 3,32
      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        Hi Anonymous 

         

        Sorry for the late reply. You can create a measure with below DAX to get the weighted average indicator. 

        New Indicator = 
VAR _table =
    ADDCOLUMNS (
        ADDCOLUMNS (
            SELECTCOLUMNS (
                'Table',
                "Quantity", 'Table'[Quantity],
                "Indicator", 'Table'[Indicator]
            ),
            "Percentage of quantity", DIVIDE ( [Quantity], SUM ( [Quantity] ) )
        ),
        "Apportionment", [Percentage of quantity] * [Indicator]
    )
RETURN
    SUMX ( _table, [Apportionment] )

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.