Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |