Forum Discussion
Post PartisanTotal value missing in calculated column
Calculated columns cannot respond to slicers. Full stop. No workaround, no clever DAX trick will change that. The moment you need a dynamic total that reacts to a filter/slicer, you need a measure. That's not optional, it's just how Power BI's engine works.
Your options are:
Push back on the "no measures" rule:show whoever made that decision this exact problem. The rule is likely a misunderstanding or an old policy that didn't anticipate this use case.
Fix it at the data source:pre-aggregate the 12-month GBP volume per client in your SQL/dataflow before it even hits Power BI. Then it's just a plain column with no aggregation ambiguity, and slicers will work naturally.
Live with the wrong total: hide the total row for that column specifically using visual formatting, so users never see the misleading number.
There is no Option 4 that involves a calculated column and a correct slicer-aware total. Anyone who tells you otherwise is wrong.Hope this helps! Don't forget to accept as solution ✅ and thumbs up 👍 in order to keep helping others.
Best regards,
Oussama (Data Consultant - Expert Fabric & Power BI)
Post PartisanHi oussamahaimoud ,
Thank you for your reply. Appreciate your time and effort 🙂
Your suggested solution has almost worked but it requires the original Vol_L12M column to still be left in the visual as shown below:
If I remove this field from the visual, which is what I ideally want to do, then the new field you suggested Vol_L12_Deduped shows wrong values (theoretcially not wrong values but values that I don't expect to see, if you know what I mean).
I have found a way to hide the original Vol_L12 (using visual formatting techniques) but what I have noticed the total value for Vol_L12_Deduped remains the same regardless of the value I select in the slicer "Dealer Team". So for example, if I choose "Risk Management" as the value for the Dealer Team slicer, the total value = 9,513,609,075.71. But when I change the value of the slicer to say "Derivatives", the total value is still showing 9,513,609,075.71 which is wrong.
Thank You,
M
Super UserHi mp390988 ,
Have you tried the calculation that I have provided in the previous post I did.
Believe that if you tweak the code you have you will have a calculation that will responde to your slicers based on the value of the column.
The main problem I identify with your column is that when you do the SUM and add the filter of the customer you get on that specific column a duplicated value that will then get incorrect totals.