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)
Super UserHi mp390988 ,
This is a question of context. Just to clarify a litle bit the question when you refer that you are not allowed to use measures on this report and you need to use calculated columns what is the meaning of this?
I would assume that this would be the other way around because calculated columns are increasing the model size and the complexity while measures are calculated at the time you call them so not adding to the size of the reports and data.
On top of that you have the question about the context calculated columns are at row level, this means that when you alter the context in this case using filter you will get a bigger number that actual you need in this case I believe that what you need if you are using a calculated column is the following code:
Vol_L12M =
IF(
FactMain[ColumnType]="Revenue" &&
FactMain[TradeDate]>= EDATE(today(), -12)
,
FactMain[GBPVolume])
I have remove the value for the Client because I assume it's a dimension table and will get the correct value based on the column you place on the visual.
Has I refer I believe that you should use a measure, if you are making a report based on a publish semantic model if you add a measure thar measure will be specific to that report so would not impact the semantic model that way you would free yourself from that rule of the column vs measure.
Check this blog posts below about Columns vs measures:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
https://www.sqlbi.com/tv/measures-vs-calculated-columns-in-dax-and-power-bi/
https://data-mozart.com/calculated-columns-vs-measures-in-power-bi/