Issues with Grand Total Calculation from Measure

[czzlglg]

Hello Power BI Community,

I am having trouble with a DAX measure that calculates fees for different business products and country combinations. While the measure works correctly at the row level, the grand total is incorrect and does not reflect the sum of the individual rows. 

Context:

I have a table in Power BI with the following structure:

• Columns:
  • business_product
  • country_code
  • date
  • volume

There is a relationship between the DataTable[date] and a DateTable[date] that holds a Year-Month value. This field is used as a filter on the dashboard page.

The aim of the analysis is to display a table that compares the fee YoY with the following structure:

• Columns:
  • business_product
  • country_code
  • Volume M (volume for the selected year-month)
  • Volume M-12 (volume for the same year-month last year)
  • Fee Income M (fees for the selected year-month)
  • Fee Income M-12 (fees for the same year-month last year)

Fee Calculation Rules:

The fees depend on both business_product and country_code as follows:

• Business product "I":
  • Country codes {"AT", "CH", "BE", "FR", "NL"} → Fee rate: 0.09
  • Country code "DE" → Fee rate: 0.07
• Business product "O": Fee rate: 0.1
• Business product "A": Fee rate: 0.09
• Business product "IC": Fee rate: 0.09

Current DAX Measure:

Here’s the measure I am using for calculating "Fee Income M-12 (EUR)":

DAX

Fee Income M-12 (EUR)= 

VAR VolumeLastYear =

    CALCULATE(

        SUM(table[volume]),

        PARALLELPERIOD(DateTable[Date], -12, MONTH)

    )

VAR SelectedCountryCode =

    MAX(table[country_code]) 

VAR SelectedBusinessProduct =

    MAX(table[business_product])  


VAR FeeRate =

    SWITCH(

        TRUE(),

        SelectedBusinessProduct = "I" && SelectedCountryCode IN {"AT", "CH", "BE", "FR", "NL"}, 0.09,

        SelectedBusinessProduct = "I" && SelectedCountryCode = "DE", 0.07,

        SelectedBusinessProduct = "O", 0.1,

        SelectedBusinessProduct = "A", 0.09,

        SelectedBusinessProduct = "IC", 0.09,

        0.0  -- Default if none of the conditions match

    )

VAR FeeIncome =

    IF(

        NOT ISBLANK(VolumeLastYear),

        VolumeLastYear * FeeRate,

        0

    )

RETURN

    SUMX(

        VALUES(table[country_code]),  -- Iterate over distinct country codes

        FeeIncome  -- Aggregate FeeIncome at the correct row level

    ) / 100  -- Apply division after aggregation, apprently PowerBI does not consider 0.09 as a decimal but as 9, that's why I use the division by 100

At the row level, the measure calculates the fees correctly, but the grand total is incorrect. The grand total does not equal the sum of the individual rows (it seems to be recalculated in a different context).

What I Want:

I want the grand total to simply be the sum of all the individual row values.

Additional Info:

Here is an example of the table I want to obtain:

Business Product	Country Code	Volume M	Volume M-12	Fee Income M (EUR)	Fee Income M-12 (EUR)
IC	DE	10	5	0.9	0.45
IC	NL	5	4	0.45	0.36
I	FR	10	8	0.9	0.72
Total		25	17	2.25	1.53

As you can see as Total I need the sum of the row values.

Question:

How can I modify my measure so that the grand total reflects the sum of individual rows instead of being recalculated in a different context?

I suspect the issue might be related to filter propagation or context transition at the total level.

LINK TO SAMPLE PBIX FILE WITH VISIBLE ISSUE ALREADY DISPLAYED: https://we.tl/t-iUyQ4CnRcL

LINK TO SAMPLE DATA TO CONNECT TO SAMPLE PBIX: https://we.tl/t-F3MBtTxixS

Any guidance on fixing this would be greatly appreciated!

Thank you!

[AMeyersen]

Hi @czzlglg ,
it seems the measure [Fee Income M (EUR)] is already correct in your example file.
If you just need the [Fee Income] for the previous year, you can simply reference the existing measure:

Fee Income M-12 Intraday (EUR) TradeDate NEW =
CALCULATE ( [Fee Income M  (EUR)], DATEADD ( DateTable[Date], -1, YEAR ) )

[Anonymous]

Hi @czzlglg ,

Please refers to the following steps.

Create a measure to store "FeeIncome".

FeeIncome = 
VAR VolumeLastYear =
    CALCULATE(
        SUM('table'[volume]),
        PARALLELPERIOD(DateTable[Date], -12, MONTH)
    )
VAR SelectedCountryCode =
    MAX('table'[country_code]) 

VAR SelectedBusinessProduct =
    MAX('table'[business product])  
    
VAR FeeRate =
    SWITCH(
        TRUE(),
        SelectedBusinessProduct = "I" && SelectedCountryCode IN {"AT", "CH", "BE", "FR", "NL"}, 0.09,
        SelectedBusinessProduct = "I" && SelectedCountryCode = "DE", 0.07,
        SelectedBusinessProduct = "O", 0.1,
        SelectedBusinessProduct = "A", 0.09,
        SelectedBusinessProduct = "IC", 0.09,
        0.0  -- Default if none of the conditions match
    )
VAR FeeIncome =
    IF(
        NOT ISBLANK(VolumeLastYear),
        VolumeLastYear * FeeRate,
        0
    )

RETURN
FeeIncome

Then, reference the [FeeIncome] measure in the [Fee Income M-12 Intraday (EUR) TradeDate] measure.

Fee Income M-12 Intraday (EUR) TradeDate = 
    SUMX(
        VALUES('table'[country_code]),  
        [FeeIncome]  
    ) / 100

The final result is as follows. The total value is equal to the sum of the values in each row.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[czzlglg]

Hi @Anonymous,

Thank you for your solution and the time you invested in it.

However, I’ve encountered an issue. Your solution works perfectly when filtering for a single business product, but the grand total remains incorrect when no filters are applied. My table needs to display all products together, not individually.

Could you please assist with this scenario? I’ve been struggling with this for almost a week and haven’t found a solution yet.

Thanks!