cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

SUM is only summing unique values, not the entire column

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])``

-

9 REPLIES 9
Community Support

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.

Solution Sage

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]))``

Helper I

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?

Solution Sage

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] ) ) )``````

Helper I

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?!

Solution Sage

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.

Helper I

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!

Solution Sage

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.

Community Champion

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors