Subtracting different matrix totals in a new table

[RichOB]

[deleted]

[Anonymous]

Hi @RichOB ,

Thank you for the proposed additions, I have made changes to the earlier measure. It is worth noting that you seem to want different results twice. As well as the fact that your results the second time do not seem to take into account positive and negative numbers. You might first consider following the steps below:

1.Add new measure.

Net_Income_Balance = 
VAR Balance_Income =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Balance"
                && 'Table'[Category ] <> "Salaries"
        )
    )
VAR Balance_Salaries =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Type] = "Balance" && 'Table'[Category ] = "Salaries" )
    )

RETURN
    ABS(Balance_Income - ABS(Balance_Salaries))

Net_Income_Budget = 
VAR Budget_Income =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Budget"
                &&  'Table'[Category ] <> "Salaries" 
        )
    )
VAR Budget_Salaries =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Type] = "Budget" && 'Table'[Category ] = "Salaries" )
    )

RETURN
    ABS(Budget_Income - ABS(Budget_Salaries))

Net_Income_Variance = 
VAR Variance_Income =
    CALCULATE (
        [Balance_1] - [Budget_1],
        FILTER (
            'Table',
            'Table'[Category ] <> "Salaries"
        )
    )
VAR Variance_Salaries =
    CALCULATE (
        [Balance_1] - [Budget_1],
        FILTER ( 'Table', 'Table'[Category ] = "Salaries" )
    )

RETURN
    ABS(Variance_Income - ABS(Variance_Salaries))

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[RichOB]

[del]

[Anonymous]

Hi @RichOB ,

Depending on the information you provided, you can follow these steps below:

1.Add new measures.

Net_Income_Balance = 
VAR Balance_Income =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Balance"
                && ( 'Table'[Category ] = "Contract Income"
                || 'Table'[Category ] = "Housing Income" )
        )
    )
VAR Balance_StaffingCost =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Type] = "Balance" && 'Table'[Category ] = "Salaries" )
    )
VAR Balance_Expenses =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Balance"
                && 'Table'[Category ] = "Rent and Rates"
        )
    )
RETURN
    Balance_Income - Balance_StaffingCost - Balance_Expenses

Net_Income_Budget = 
VAR Budget_Income =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Budget"
                && ( 'Table'[Category ] = "Contract Income"
                || 'Table'[Category ] = "Housing Income" )
        )
    )
VAR Budget_StaffingCost =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[Type] = "Budget" && 'Table'[Category ] = "Salaries" )
    )
VAR Budget_Expenses =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Type] = "Budget"
                && 'Table'[Category ] = "Rent and Rates"
        )
    )
RETURN
    Budget_Income - Budget_StaffingCost - Budget_Expenses

Net_Income_Variance = 
VAR Variance_Income =
    CALCULATE (
        [Balance_1] - [Budget_1],
        FILTER (
            'Table',
            'Table'[Category ] = "Contract Income"
                || 'Table'[Category ] = "Housing Income"
        )
    )
VAR Variance_StaffingCost =
    CALCULATE (
        [Balance_1] - [Budget_1],
        FILTER ( 'Table', 'Table'[Category ] = "Salaries" )
    )
VAR Variance_Expenses =
    CALCULATE (
        [Balance_1] - [Budget_1],
        FILTER ( 'Table', 'Table'[Category ] = "Rent and Rates" )
    )
RETURN
    Variance_Income - Variance_StaffingCost - Variance_Expenses

2.Put in Values in Matrix.

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[RichOB]

[del]

[Anonymous]

Hi @RichOB ,

Can you provide more detailed data (without sensitive information) and combine it with the results you expect to get. This would allow us to better understand and address the issues you are experiencing.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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