calculate formula

Question

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 Column = CALCULATE(SUM('customer payment data'[ Customer Amount]),Table1[Index]) but this is only pull down part of the values.

I have tried to add screenshots but for some reason i can't get these to upload.

habib · Answer

Apply filter on your referenced table within CALCULATE funtion it might work as I am not sure about exact error.

andywil456 · Answer

Hi Habib, Below is a screenshot of the table i have created and the error message.What i'm after is a breakdown of the total supplier spend by payment category.&nbsp;&nbsp;

habib · Answer

andywil456CALCULATE function expect filter as second parameter but in your scenario your are providing column value. Please add some filter option like ALL or ALLSELECTED.

andywil456 · Answer

Thank you&nbsp;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.&nbsp;The step up between each table is the same apart when&nbsp;i select "make this relationship active" i get this error message&nbsp;.&nbsp;

jahida · Answer

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.&nbsp;In terms of the CALCULATE formula, if you want to keep it simple, just go with:&nbsp;CALCULATE(SUM('supplier pymt data'[Supplier Amount]), ALL('supplier pymt data'), 'supplier pymt data'[payment category] = Table1[payment category])&nbsp;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 -&gt; Date -&gt; Supplier payment, which is giving you unexpected results. Switching to 1-way filtering should fix that. So would the ALL statement above.

Forum Discussion

calculate formula

6 Replies

Related content

If formula

formula

DAX Formula Help

Formula to calculate YoY

Dax Formula calculations