Forum Discussion

LunaD's avatar
LunaD
Regular Visitor
2 years ago
Solved

Subtract values in rows based on categories in another table

I have table 1 Date ID Value 23/02/2024 x234fs 21 23/02/2024 d4635d 2 23/02/2024 a324ss 54 23/02/2024 d35s22 3   table 2 Category ID Location TypeA x234fs La...
Need Help
  • LunaD's avatar
    LunaD
    2 years ago

    Thanks for your suggestion amitchandak !!
    It's very close to what I need, just that it won't work if there are different dates in the table.
    A little tweak fixed it:

    Measure = if(max(Table2[Category]) ="TypeA", sum(Table1[Value])- CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1, Table2[Location], Table1[Date]), Table2[Category]="TypeB"), SUM(Table1[Value]))
     
    Cheers.
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

LunaD , Hope two tables are joined. File is attached for reference 

 

Measure = if(max(Table2[Category]) ="TypeA", sum(Table1[Value])- CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1, Table2[Location]), Table2[Category]="TypeB"), SUM(Table1[Value]))