Search code examples
exceldaxpowerpivot

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

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?

Solution

  • 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.

    Value Field Settings