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 

. 

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

andywil456

 

In this scenario, since you have build the relationship between Table1 and 'supplier pymt data', you just need to use ALLEXCEPT to get the data from 'supplier pymt data'  group on [payment category]. The DAX can be like:

 

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

See my sample below:

Regards,