Total value missing in calculated column

Hi 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/

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)

Hi 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