Filter-weighted average

[Syndicate_Admin]

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.

Year	Area	Center	Wine cellar	Quantity	Indicator
2018	Eleventh	Rabudos	101	48.029	1,98
2018	Eleventh	Rabudos	102	42.947	3,60
2018	Eleventh	Rabudos	102	13.080	2,40
2018	Eleventh	Rabudos	103	50.800	1,98
2018	Eleventh	Rabudos	104	49.300	3,64
2018	Eleventh	Rabudos	105	50.800	2,42
2018	Eleventh	Rabudos	106	49.856	3,64
2018	Eleventh	Rabudos	107	50.800	2,13
2018	Eleventh	Rabudos	108	51.744	1,50
2018	Eleventh	Rabudos	109	50.800	2,13
2018	Eleventh	Rabudos	110	22.556	1,61
2018	Eleventh	Rabudos	110	28.244	1,50
2018	Eleventh	Rabudos	111	50.800	1,50
2018	Eleventh	Rabudos	112	51.061	1,43
2018	Eleventh	Rabudos	113	50.800	1,50
2018	Eleventh	Rabudos	114	25.830	1,25
2018	Eleventh	Rabudos	114	24.383	1,61
2018	Eleventh	Rabudos	115	50.829	3,00
2018	Eleventh	Rabudos	116	50.944	1,50
2018	Eleventh	Rabudos	117	50.810	3,30
2018	Eleventh	Rabudos	118	50.587	1,65
2020	Eleventh	Rabudos	101	51.704	3,84
2020	Eleventh	Rabudos	102	50.800	2,45
2020	Eleventh	Rabudos	103	51.687	3,37
2020	Eleventh	Rabudos	104	50.800	3,70
2020	Eleventh	Rabudos	105	51.400	3,08
2020	Eleventh	Rabudos	106	41.300	1,21
2020	Eleventh	Rabudos	106	4.000	2,60
2020	Eleventh	Rabudos	106	5.500	2,86
2020	Eleventh	Rabudos	107	50.800	3,81
2020	Eleventh	Rabudos	108	50.800	3,38
2020	Eleventh	Rabudos	109	50.800	3,36

[v-jingzhang]

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]

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

Year	Center	Wine cellar	Quantity	Indicator	Percentage of quantity	Apportionment
2018	Rabudos	102	42947	3,6	77%	2,75954808
2018	Rabudos	102	13080	2,4	23%	0,56030128
			56027			3,32

[v-jingzhang · Accepted solution]

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]

Thank you very much, it was perfect