Forum Discussion

Firas_Belgouthi's avatar
Firas_Belgouthi
Regular Visitor
2 years ago

Problem with Matrix total rows is not summing correctly

Hi,
I'm creating a balancesheet Matrix.
I have a fact table in which i have gl_account's transactions overtime 

OB =
CALCULATE(
        DIVIDE(SUM('FACT'[OB]), COUNT('FACT'[OB])),
        ALLEXCEPT('FACT', 'FACT'[GL_ACCOUNT_DESCRIPTION],'FACT'[ACCOUNTGROUP],'FACT'[Account_Type],'FACT'[PARTICULARS])
    )


CB =
VAR Total_T  = [FTM]    
VAR Average_OB = DIVIDE(SUM('FACT'[OB]), COUNT('FACT'[OB]))
Var r =
    Total_T +
    CALCULATE(
        Average_OB,
        ALLEXCEPT('FACT', 'FACT'[GL_ACCOUNT_DESCRIPTION],'FACT'[ACCOUNTGROUP],'FACT'[Account_Type],'FACT'[PARTICULARS])
    )
return r

[FTM] is the sum of trunsactions


 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Firas_Belgouthi , You need to have measures like

     

    CB =
    SUMX(
    Summarize('FACT', 'FACT'[GL_ACCOUNT_DESCRIPTION],'FACT'[ACCOUNTGROUP],'FACT'[Account_Type],'FACT'[PARTICULARS], DimDate[Year Month]),
    VAR Total_T = [FTM]
    VAR Average_OB = DIVIDE(SUM('FACT'[OB]), COUNT('FACT'[OB]))
    RETURN
    Total_T +
    CALCULATE(
    Average_OB,
    ALLEXCEPT('FACT', 'FACT'[GL_ACCOUNT_DESCRIPTION], 'FACT'[ACCOUNTGROUP], 'FACT'[Account_Type], 'FACT'[PARTICULARS])
    )
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Firas_Belgouthi 

     

    In matrix and table visuals, the total is calculated on the underlying data, not simply sum up the visible results in the visual. If you want to sum up the row results into Total row, you can follow a pattern introduced in the following links:

    Solved: Need to summarize column with parameters - Microsoft Fabric Community

    Obtaining accurate totals in DAX - SQLBI

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!