Forum Discussion

RichOB's avatar
RichOB
Post Partisan
10 months ago
Solved

Changing the total calculation in table

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...
Need Help
  • Anonymous's avatar
    Anonymous
    10 months ago

    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's avatar
RichOB
Post Partisan
10 months ago

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!