How to multiply two filtred values in one column and then sum them together

[Anonymous]

I have the following data:

ID	Location	Metric	Value
1	A	Population	1000
2	A	Average Income	50000
3	B	Population	2000
4	B	Average Income	30000
5	C	Population	5000
6	C	Average Income	20000

How do I get the following result:

sum(Value of Population * Value of Average Income) / sum(Value of Population)

So when I select locations A and B, it should calculate each location's Value of Population * Value of Average Income then sum them together.

Thanks.

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

Sum PXAV = 
VAR _A =
    SUMMARIZE (
        'Table',
        'Table'[Location],
        "PXAI",
            CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Metric ] = "Population" )
                * CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Metric ] = "Average Income" )
    )
RETURN
    SUMX ( _A, [PXAI] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!

LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Thank you, this is exactly what I need.