Forum Discussion

mp390988's avatar
mp390988
Icon for Post Partisan rankPost Partisan
5 months ago
Solved

Total value missing in calculated column

Hi,   I am having an issue in getting the total value displayed in the red circle I have marked in this snapshot. A little bit of context: that last column is caclulated column. We are not allowed...
Need Help
  • oussamahaimoud's avatar
    oussamahaimoud
    5 months ago

    mp390988,

     

    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)

oussamahaimoud's avatar
oussamahaimoud
Icon for Memorable Member rankMemorable Member
5 months ago

Hi mp390988,

Hope you're doing well!

 

Since you can't use measures, you need to prevent double-counting at the total row. The trick is to create a second calculated column on the same table that returns the value only once per unique client, and zeros out duplicates. But actually the cleaner and fully supported workaround is this:

 

Create a helper calculated column on Clients_Snowflake:

 

Vol_L12M = 

CALCULATE(

    SUM(FactMain[GBPVolume]),

    FILTER(

        ALL(FactMain),

        FactMain[ColumnType] = "Revenue" &&

        FactMain[ClientID] = EARLIER(Clients_Snowflake[ClientID]) &&

        FactMain[TradeDate] >= EDATE(TODAY(), -12)

    )

)

 

This alone won't fix the total. So for the total row to be correct, you need to change the column summarization to "Minimum" (or Maximum) instead of Sum. But for the grand total, Minimum across all clients would still be wrong. So the real complete solution is:

 

Column 1 (already have this, Vol_L12M, keep as is)

Column 2 on Clients_Snowflake, deduplicated flag:

 

Vol_L12M_Deduped = 

VAR CurrentClient = Clients_Snowflake[ClientID]

VAR RowRank = 

    RANKX(

        FILTER(Clients_Snowflake, Clients_Snowflake[ClientID] = CurrentClient),

        Clients_Snowflake[Vol_L12M],

        ,

        ASC,

        Dense

    )

RETURN

IF(RowRank = 1, Clients_Snowflake[Vol_L12M], 0)

 

Then use Vol_L12M_Deduped in the visual with SUM, each client's value only contributes once to the total.

 

Hope this helps! Don't forget to accept/mark as solution and thumbs up 👍 in order to keep helping others.

 

Best regards,

Oussama (Data Consultant - Expert Fabric & Power BI)