Forum Discussion

andywil456's avatar
andywil456
Icon for Advocate II rankAdvocate II
10 years ago

calculate formula

I trying to pull through the total sales from another table but i want it to show the break down as per each payment category which is cross referenced by index numbers. The formula i'm using is Colu...
andywil456's avatar
andywil456
Icon for Advocate II rankAdvocate II
10 years ago

Thank you Habib, now i'm getting another issue. Using either filter ALL or ALLSELECTED i'm getting a total value of £26m but the supplier amounts should only total £8.5m.

I have applied the same formula to my customer amounts and they match. 

The step up between each table is the same apart when i select "make this relationship active" i get this error message 

. 

jahida's avatar
jahida
Icon for Impactful Individual rankImpactful Individual
10 years ago

If you want that relationship to work you need to make many of the other relationships (and that one) one-way. An easy way to think about is that there should be no loops in your relationships (cycles in graph terminology). The relationship you're trying to create would create a loop.

 

In terms of the CALCULATE formula, if you want to keep it simple, just go with:

 

CALCULATE(SUM('supplier pymt data'[Supplier Amount]), ALL('supplier pymt data'), 'supplier pymt data'[payment category] = Table1[payment category])

 

Then you don't have to worry about the relationship at all (for this step at least). The reason you're getting a weird result right now is that because of the 2-way filtering, the filters are going through the Customer payment -> Date -> Supplier payment, which is giving you unexpected results. Switching to 1-way filtering should fix that. So would the ALL statement above.