cancel
Showing results for
Search instead for
Did you mean:
dandutch Frequent Visitor

## Calculating the sum of values, only once per ID and filtered by another table

Dear all,

not sure if my title makes sense, but I'll try to explain.

Description

I've got two tables:

Table A has the information I would like to display in Table B. Name in Table A is always blank on Level 1.0. All Names of Table B are in Table A, but Table A might have more names. Also, some Names of Table B could be Manager. There is always only one Manager per ID, but there might be different names per ID.

Active relationship: Table A[Manager] (many side) : Table B [Name ] (one side)

Deactivated relationship: Table A [Name] (many side) : Table B[Name] (one side)

By default, I would like to keep the currently active relationship, because I do other calculations with it. So, If required, the relationship should be changed within the calculation.

Table A

 Level ID Value Manager Name 1.0 123 100 Z 1.1 123 50 Z A 1.1.1 123 30 Z A 1.1.2 123 20 Z B 1.2 123 50 Z E 1.0 456 300 C 1.1 456 150 C C 1.2 456 100 C D 1.3 456 50 C E 1.0 789 400 X 1.1 789 300 X A 1.2 789 100 X C

Table B

 Name A B C D E

Task

I want to use the Name of Table B and get the sum of Value for Level 1.0, only once per ID for the current Name.

e.g.: Name A is present twice for ID123, but Value should be used only once. However, Value should be used again for Name B and E.

Table B (example for ID123)

 Name Value A 100 B 100 C 0 D 0 E 100

To make it even more complex: if the current Name is equal to Manager, Value should not be used for this name. But, still for others

Table B (example for ID456)

 Name Value A 0 B 0 C 0 D 300 E 300

Table B (final result)

Explanation is not part of the table.

 Name Sum of Value Level 1.0 Explanation A 500 Value (ID123, Level 1.0) + Value (ID789, Level 1.0) B 100 Value (ID123, Level 1.0) C 400 Value(ID789, Level 1.0) D 300 Value (ID456, Level 1.0) E 400 Value (ID123, Level 1.0) + Value (ID456, Level 1.0)

I have been trying to figure this out in DAX the whole week. I hope my explanation is clear and you understand what I am trying to do. I am looking forward to your suggestions on this.

many thanks!

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
dandutch Frequent Visitor

## Re: Calculating the sum of values, only once per ID and filtered by another table

@v-yuta-msft

I have managed to create a calculated column in Table B which calculates value correctly. The only exception is, that I have not yet taken Manager into account. I would still appreciate some feedback whether this is the best solution in terms of readability and performance.

Value =

```SUMX (
CALCULATETABLE (
FILTER (
FILTER ( ALL ( 'Table A' ); ( 'Table A'[ID] IN VALUES ( 'Table A'[ID] ) ) );
'Table A'[Level] = "1.0"
);
FILTER ( 'Table A'; SEARCH ( 'Table B'[Name]; 'Table A'[Name];; 0 ) > 0 )
);
'Table A'[Value]
)```

many thanks.

3 REPLIES 3 Community Support Team

## Re: Calculating the sum of values, only once per ID and filtered by another table

Task

I want to use the Name of Table B and get the sum of Value for Level 1.0, only once per ID for the current Name.

e.g.: Name A is present twice for ID123, but Value should be used only once. However, Value should be used again for Name B and E.

Table B (example for ID123)

 Name Value A 100 B 100 C 0 D 0 E 100

To make it even more complex: if the current Name is equal to Manager, Value should not be used for this name. But, still for others

Table B (example for ID456)

 Name Value A 0 B 0 C 0 D 300 E 300

Table B (final result)

Explanation is not part of the table.

 Name Sum of Value Level 1.0 Explanation A 500 Value (ID123, Level 1.0) + Value (ID789, Level 1.0) B 100 Value (ID123, Level 1.0) C 400 Value(ID789, Level 1.0) D 300 Value (ID456, Level 1.0) E 400 Value (ID123, Level 1.0) + Value (ID456, Level 1.0)

Could you clarify more details about the logic to generate Table B(example for ID123) and Table B(example for ID456)?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dandutch Frequent Visitor

## Re: Calculating the sum of values, only once per ID and filtered by another table

@v-yuta-msft

please find the explanations below.

Explanation for ID123

 Name Value Explanation (ID123) A 100 Name A is present twice for ID123, but value (ID123, Level 1.0) should be used only once B 100 Name B is present once for ID123, therefore value (ID123, Level 1.0) should be used C 0 Name C is not present for ID123, therefore 0 D 0 Name D is not present for ID123, therefore 0 E 100 Name E is present once for ID123, therefore value (ID123, Level 1.0) should be used

Explanation for ID456

 Name Value Explanation (ID456) A 0 Name A is not present for ID456, therefore 0 B 0 Name B is not present for ID456, therefore 0 C 0 Name C is present for ID456, but Name C is also Manager for ID456, therefore 0 D 300 Name D is present once for ID456, therefore value (ID456, Level 1.0) should be used E 300 Name E is present once for ID456, therefore value (ID456, Level 1.0) should be used

If possible, I would prefer a calculated column to use it for additions with other columns.

What I have been trying to do is to get a list of IDs where the current Name is present and where current Name is not equal to Manager. Then I would filter Table A by these IDs and summarize all values only for Level 1.0 and only once per ID. This should be iterated for all names in Table B. The Problem is if Table A is filtered by the current name from Table B, all rows with Level 1.0 will be filtered as well because Name in Table A is always empty for Level 1.0. Therefore, I would need to Filter original Table A by the results (IDs) of Table A filtered by Name. I think this requires nested filters, but I am getting confused with context.

Example Name A

Step 1: Filter Table A by Name from Table B

 Level ID Value Manager Name 1.1 123 50 Z A 1.1.1 123 30 Z A 1.1 789 300 X A

Step 2: Use IDs for Name A (123 + 789) to filter original Table A

 Level ID Value Manager Name 1.0 123 100 Z 1.1 123 50 Z A 1.1.1 123 30 Z A 1.1.2 123 20 Z B 1.2 123 50 Z E 1.0 789 400 X 1.1 789 300 X A 1.2 789 100 X C

Step 3. Filter by Level = 1.0 (Table A)

 Level ID Value Manager Name 1.0 123 100 Z 1.0 789 400 X

Step 4. Summarize Values (result for Table B)

 Name Value A 500

Step 5: Repeat 1-4 for all Names in Table B

hope this helps.

dandutch Frequent Visitor

## Re: Calculating the sum of values, only once per ID and filtered by another table

@v-yuta-msft

I have managed to create a calculated column in Table B which calculates value correctly. The only exception is, that I have not yet taken Manager into account. I would still appreciate some feedback whether this is the best solution in terms of readability and performance.

Value =

```SUMX (
CALCULATETABLE (
FILTER (
FILTER ( ALL ( 'Table A' ); ( 'Table A'[ID] IN VALUES ( 'Table A'[ID] ) ) );
'Table A'[Level] = "1.0"
);
FILTER ( 'Table A'; SEARCH ( 'Table B'[Name]; 'Table A'[Name];; 0 ) > 0 )
);
'Table A'[Value]
)```

many thanks.