How to calculate two columns based on another?

[CalvinL]

Hi all,

Looking to calculate two values based off of another column.

Example:

I want to calculate the sales of shop1 and shop2 but only if it is in New York.

ID	City	Shop1Sales	Shop2Sales
1	New York	40	30
2	Chicago	20	35
3	New York	50	10
4	Chicago	10	10

So the result should be in a new column:

TotalNewYorkSales

70

0

60

0

Any help appreciated.

[amitchandak]

@CalvinL , try a measure like

TotalNewYorkSales = sumx(filter(Table, Table[City] = "New York"), Table[Shop1Sales]+ Table[Shop2Sales])+0

[mahoneypat · Accepted solution]

I encourage you to unpivot your data, but, if not, you can try use this measure expression

NY Sales = CALCULATE(SUMX(Table, Table[Shop1Sales] + Table[Shop2Sales]), KEEPFILTERS(Table[City] = "New York")) + 0

Regards,

Pat