Forum Discussion

CalvinL's avatar
CalvinL
Icon for Helper II rankHelper II
5 years ago
Solved

How to calculate two columns based on another?

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.

IDCityShop1SalesShop2Sales

1

New York4030
2Chicago2035
3New York5010
4Chicago1010

 

So the result should be in a new column:

TotalNewYorkSales

70

0

60

0

 

Any help appreciated.

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    CalvinL , try a measure like

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