SUM is only summing unique values, not the entire column

[bigchippah]

Incorrect sum of output!

To begin: I have a table with hourly output totals from manufacturing facilities.  I would like to compare totals of Actual Output versus Expected Output.

Table 1: Actual Output totals (output-table)

Hour	Location	# of Facilities Operating	Actual Output	Output ID
10am	Ohio (102)	5	92	102-5
11am	Ohio (102)	5	98	102-5
12pm	Ohio (102)	5	91	102-5
1pm	Ohio (102)	5	75	102-5
2pm	Ohio (102)	4	38	102-4
3pm	Ohio (102)	4	33	102-4

This is related (Output ID -----> Output ID) to a table that shows how much Output we should be expecting:

Table 2: Output Expectations (expectation-table)

Location	# of Facilities Operating	Expected Output	Output ID
Ohio (102)	5	100	102-5
Ohio (102)	4	83	102-4
Ohio (102)	3	62	102-3

When I add these two fields into the same visual, I am hoping to see totals the Facilities Operating, as well as the Expected Output.  But my Expected Output total appears to ONLY total the unique values.

Visual: Table with Incorrect Totals

Hour	Location	Facilities Operating	Capacity ID	Expected Output
10am	Ohio (102)	5	102-5	100
11am	Ohio (102)	5	102-5	100
12pm	Ohio (102)	5	102-5	100
1pm	Ohio (102)	5	102-5	100
2pm	Ohio (102)	4	102-4	83
3pm	Ohio (102)	4	102-4	83
Total		28		183

Issue: Expected Output SHOULD display 566 (100+100+100+100+83+83), not 183 (100+83).  Why is my total only summing the unique values and not the entire column?  I feel like I'm missing something completely obvious.

I'm attempting to SUM with the following code:

Output Expectations = SUM(output-table[Expected Output])

-

[v-henryk-mstf]

Hi @bigchippah ,

According to your description, circular dependencies is a common problem, which usually arises in the modeling process, you can refer to the following blog.

Understanding circular dependencies in DAX - SQLBI

In the Power BI whirlpool — understanding circular dependencies | by Nikola Ilic | Towards Data Scie...

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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

[AntonioM]

The total is giving you the sum for all the IDs in the total. There are two (102-5 and 102-4) so it finds the expected output for those and then adds them up, giving you 100 + 83. 

You should be able to use SUMX instead of SUM, to through each row in the table and get the corresponding expected output, including all the duplicates. Something like

Output Expectations = SUMX('output-table', RELATED('expectation-table'[Expected Output]))

[bigchippah]

No dice.  Still only showing as "183."

I keep receiving an error about the "expectation table "either doesn't exist or doesn't have a relationship."  But "expectation-table" and "output-table" are related via the OutputID field.

The Expected Output field is in a different table (expectation-table) from the actual hourly output data (output-table).  Should I be trying something like this instead?

SUMX('expectation-table', 'output-table'[Hour])

That clearly doesn't work, but I'm trying to SUM the Expected Output (depending on the Output ID) by Hour.  Am I off base?

[AntonioM]

Which way is the relationship between them?

One other option (which could be best going forward) is to create a separate table of IDs, then link that to both the actual and expected tables. Then you'd use the id from that table in your visual and then get the sums from the other tables.

You could do that with

Table =
DISTINCT ( UNION ( VALUES ( Actual[OrderID] ), VALUES ( Expected[OrderID] ) ) )

[bigchippah]

Hi Antonio,

You explained that very succintly and I understand the intent, but I have absolutely no idea how to make it work. 

I tried your code and it gave me a singular table containing JUST the CapacityID's.  I can't create relationships from (actual[OrderID])-->(table[OrderID])-->(expected[OrderID]) because I'm getting a "circular dependency was detected" error message.

What am I doing wrong?!

[AntonioM]

I've had a fiddle with things and I managed to get to this point with the data in your post. 

Expected and the new table have 1 row for each Output ID, so it creates a 1-1 relationship, which it doesn't seem to like. To get round this I added a row to your expected data with OutputID "102 -3", so that it created a 1-many relationship. I've then removed that row after setting this up and it seems to be fine.

This worked for me just now, not sure if there might be a better way to overcome this.

[bigchippah]

Can't make it work.  When I try many-to-one or one-to-many relationships between ExpectedOutput and the Table, I get a "cardinality isn't valid" message.  And when I try to make ANY relationship between the Table and ActualOutput, I get "a circular dependency was detected" message.

This shouldn't be this hard!

[AntonioM]

No, you'd think not. 

One other thing I would suggest, you could instead make the table of IDs in Power Query. You'd need to duplicate the queries, remove all the other columns, append them together and then remove duplicates.

Once the data is refreshed from Power Query, how the table is made doesn't have any effect, so there shouldn't be any dependancy problems anymore.

[pranit828]

Hi @bigchippah 

The results are also controlled by the slicers and filters. Please check what you have available in the report.