Changing the total calculation in table

[RichOB]

Hi, how can I change the total in the Family Payment column to show £7045.38, not "260.94? The measure for this column is:

Family_Payment = CALCULATE( SUM( 'Table1'[Amount] ), 'Table1'[Desription] = "Family Payment" )

Thanks

[Poojara_D12]

Hi @RichOB 

The reason your Family Payment column is showing an incorrect total (such as £260.94 instead of £7045.38) is because of how Power BI handles totals for measures. Unlike calculated columns, measures are evaluated in the current filter context of each row or cell — and at the total level, that context is removed, so your condition 'Table1'[Description] = "Family Payment" no longer filters correctly. As a result, Power BI calculates the sum using the total context rather than summing up the visible “Family Payment” row values. To fix this and make the total display correctly, you can modify your DAX measure to calculate the total explicitly across all rows that meet your condition, regardless of visual context. Try this revised version:

Family_Payment =
SUMX(
    FILTER('Table1', 'Table1'[Description] = "Family Payment"),
    'Table1'[Amount]
)

This formula uses SUMX with a FILTER function to iterate through all rows of 'Table1' where the description equals “Family Payment,” and sums their amounts directly. By doing this, both the individual rows and the total in your visual will show the correct value (£7045.38), since the logic now applies consistently at every level of aggregation.

[v-tsaipranay]

Hi @RichOB ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @RichOB ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay · Accepted solution]

Hi @RichOB ,

Thanks for reaching out to the Microsoft fabric community forum. 

After thoroughly reviewing the details you provided, I try to workon my end. I have used some sample data on my end and successfully implemented it.     

I am also including .pbix file for your better understanding, please have a look into it:

Hope this resolves your issue. Please feel free to reach out for any further questions.

Thank you 

[RichOB]

Hi Ivanka, thanks for your reply. For some reason, i'm unable to add a pbix file, but here is how the table data is arranged from the source:

Here is the government payment measure that splits up the monthly payment per week:

Government_Payment_Needed = 
VAR _Week = MAX( 'Table1'[Week Number] )
VAR _LastGovPaymentWeek = CALCULATE( MAX( 'Table1'[Week Number] ), 'Table1'[Desription] = "Government Payment", 'Table1'[Week Number] <= _Week )
VAR _LastGovPayment = CALCULATE( MAX( 'Table1'[Amount] ), 'Table1'[Desription] = "Government Payment", 'Table1'[Week Number] <= _LastGovPaymentWeek )
VAR _Return = _LastGovPayment / 4
RETURN _Return

Here's the week number measure:

Week Number (FY) =

VAR CurrentDate = 'Table1'[Date]

VAR FYStart =

    DATE(

        YEAR(CurrentDate) - IF(MONTH(CurrentDate) < 4, 1, 0),

        4,

        1

    )

RETURN

INT( ( CurrentDate - FYStart ) / 7 ) + 1

Thanks!

[ivana_tomekova]

Hi... this look strange, because formula looks OK.... so problem would be probably somewhere on model level.... can you share your pbix file?... or what is the formula for Government payment?... do you have data in Table1 aggregated by year week?.... to me it looks like that week specification in Table1 is missing, therefore for every week and also for total just same amount is used.