Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
bigchippah
Helper I
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)

HourLocation# of Facilities OperatingActual OutputOutput ID
10amOhio (102)592102-5
11amOhio (102)598102-5
12pmOhio (102)591102-5
1pmOhio (102)575102-5
2pmOhio (102)438102-4
3pmOhio (102)433102-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 OperatingExpected OutputOutput ID
Ohio (102)5100102-5
Ohio (102)483102-4
Ohio (102)362102-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

HourLocationFacilities OperatingCapacity IDExpected Output

10am

Ohio (102)5102-5100
11amOhio (102)5102-5100
12pmOhio (102)5102-5100
1pmOhio (102)5102-5100
2pmOhio (102)4102-483
3pmOhio (102)4102-483
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
v-henryk-mstf
Community Support
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.

AntonioM
Solution Sage
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]))

 

 

 

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?

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

 

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

 

 

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

 

AntonioM_0-1658937066170.png

 

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.

 

 

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!

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
Community Champion
Community Champion

Hi @bigchippah 

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





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors