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 repea...
  • 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.

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you very much, it was perfect