Get distinct value from multiple rows based on key from other column

Question

I have two tables with a relation to the attribute Sys_ID in Excel PowerPivot. Sys_Value in Table2 is a Lookup from Table1 ( =Related(Table1[Sys_Value]) )

Table1

Sys_ID  Sys_Value
Sys-1   10
Sys-2   20

Table2

ID  Org_ID  Sys_ID_FK   Sys_ValueLookUp
1   Org-1   Sys-1       10
2   Org-2   Sys-1       10
3   Org-3   Sys-1       10
4   Org-2   Sys-2       20
5   Org-3   Sys-2       20

In a PowerPivot chart, I need Sys_ID_FK, Sys_Value_LookUp and to filter on Org_ID

I am getting the following result in the pivot chart/table:

Filter: Not set (all)
Result:

Sys-1   30
Sys-2   40

This is wrong and the correct result should be:

Filter: Not set (all)
Result: 

Sys-1   10
Sys-2   20

or second example

Filter: Org-1
Result:

Sys-1   10

How can I get a result that is counting only one value per "Sys"? Or is there a way to apply the Org-filter from table2 to table1?

Alexis Olson Alexis Olson · Accepted Answer · 2018-04-27T14:18:33

The pivot table is summing the Sys_Value_Lookup for all selected rows. If you don't want that, then you can switch the aggregation to Max instead of Sum under the Value Field Settings.

Get distinct value from multiple rows based on key from other column

1 Answers