Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Apply filter on your referenced table within CALCULATE funtion it might work as I am not sure about exact error.
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.
@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.
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
.
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,
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |