Issue: Formula Works in Card, but Breaks Table

[paulsnet1986]

Table 1 (Source: Internal Database)

Equipment Record Number

Expected COGS

Table 2 (Source: Financial Reporting)

Equipment Record Number

Actual COGS

One-to-many relationship: there are multiple line items per Equipment Record Number in table two.

Formula: Cost Variance = SUM('Table 1'[Expected COGS]) - SUM('Table 2'[Actual COGS])

When added to a card, I get the expected total variance: $269.

But when I add the formula to my table, it creates thousands of lines of the same record and value, when there are only a few hundred records in total.

[Icey · Accepted solution]

Hi @paulsnet1986 ,

I can reproduce your issue when use the "Equipment Record Number" column from Table 2, instead of Table 1. Please use the ERN column from Table 1, not Table 2.

Best Regards,

Icey

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

[Ashish_Mathur]

Hi,

Create a third table (Table3) with a single column which has all unique Equipment record numbers.  Create a relationship between the ERN number of Table1 and Table2 to Table3.  To your visual, drag ERN from Table3.  Drag your measure to the visual.

Hope this helps.

[Greg_Deckler]

@paulsnet1986 Are the tables related to one another? Likely you are getting a cartesian product of all the rows in both tables. Something along those lines.