Calculated filter not working as intendet

[Urganot]

I have the following tables


Orders:

    OrderID|Cost|Quarter|User
    -------------------------
       1   | 10 |   1   | 1
       2   | 15 |   1   | 2
       3   |  3 |   2   | 1
       4   |  5 |   3   | 3
       5   |  8 |   4   | 2
       6   |  9 |   2   | 3
       7   |  6 |   3   | 3

Goals:

    UserID|Name |Goal|Quarter|Sum
    -----------------------------
       1  |John | 20 |   1   | x
       1  |John | 15 |   2   | x
       2  |Bob  | 12 |   2   | x
       2  |Bob  | 15 |   3   | x
       3  |Homer|  5 |   3   | x
       3  |Homer|  7 |   4   | x

Now what I want to do is in the second table sum up all orders that have the UserID from the current row and are in the current rows quarter.


My first attempt was:

=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=[Quarter];Orders[User]=[UserID])

The result should be:

    UserID|Name |Goal|Quarter|Sum
    -----------------------------
       1  |John | 20 |   1   | 10
       1  |John | 15 |   2   |  3
       2  |Bob  | 12 |   2   | 15
       2  |Bob  | 15 |   3   |  8
       3  |Homer|  5 |   3   | 11
       3  |Homer|  7 |   4   |  0

But here I get the error:

Column "User ID" cannot be found or may not be used in this expression.

To better explain what it should do, here are the lines how they should be executed for each line.

=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=1;Orders[User]=1)
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=1)
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=2)
=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=3;Orders[User]=2)
.
.
.

To try something out, I used fixed values:

=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=1)

Which resulted not in an error message but not fully working either. I now get

    UserID|...|Sum
--------------
       1  |...| 3
       1  |...| 3
       2  |...| 
       2  |...| 
       3  |...| 
       3  |...|

FYI: the empty space in SUM is really empty.


The crazy thing is if I do the following:

=CALCULATE(sum(Orders[Cost]);Orders[Quarter]=2;Orders[User]=Orders[User])

I get:

    UserID|...|Sum
      --------------
       1  |...|  3
       1  |...|  3
       2  |...| 
       2  |...| 
       3  |...| 11
       3  |...| 11

Which is almost what I want. But I dont understand, why some cells are working.


I hope its understandable what my problem is.

 

[gsistek]

Try the following:
=CALCULATE(SUM(Orders[Cost]);FILTER(Orders;Orders[Quarter]=[Quarter]&&Orders[User]=[UserID]))

 

[mozartiano]

I suggest creating a measure with the following formula.

FIELD1=CALCULATE(SUM(Orders[Cost]);ALLEXCEPT(Orders;Orders[Quarter];Orders[UserID]))

This should dinamically calculate the sum whenever those 2 contexts (Quarter and Orders) appear in the table.