Forum Discussion

AzadHGS's avatar
AzadHGS
Regular Visitor
1 year ago
Solved

Incorrect Total in Matrix Visual

Hi Team,

I'm currently working with a matrix visual that includes four measures: Actual, Plan, BIAS (calculated as Plan - Actual), and BIAS % (percentage change of Plan - Actual). While the individual row values are displaying correctly, I'm facing an issue with the row totals.

Specifically, the totals for Actual and Plan are accurate as they are simply summing the values. However, the totals for BIAS and BIAS % are not showing the sum of the individual row values. Instead, they are calculated as total Plan minus total Actual, which is not the expected behavior. Ideally, the BIAS total should represent the sum of all individual BIAS values across the rows, not a KPI-style aggregation.

I've attached the DAX measures and a snapshot of the visual for reference. Could you please take a look and let me know how this can be resolved?

Best regards,
Azad

  • Nasif_Azam's avatar
    Nasif_Azam
    1 year ago

    Hey AzadHGS ,

    The issue you’re encountering with incorrect totals in your matrix visual for the BIAS and BIAS % rows is a common DAX behavior. In Power BI matrix visuals:

    • Total rows are not the sum of individual values you see in the matrix.

    • Instead, DAX re-evaluates the measure at the total level using total Plan and total Actual, not summing each row's calculated result.

    That’s why you're seeing:

    • GSV $ BIAS (Plan vs Act) total as = 41,16,757 - 16,75,782 = 24,40,975
      instead of the expected sum of individual biases.

    Fix Totals Using DAX ISINSCOPE or HASONEVALUE

    You can customize your BIAS measures to show summed values in totals rather than default totals using ISINSCOPE() or HASONEVALUE().

    GSV $ BIAS (Plan vs Act) =
IF(
    ISINSCOPE('DateTable'[Period]),
    [Plan Ship Dollar] - [Actual Ship Dollar],
    SUMX(
        VALUES('DateTable'[Period]),
        [Plan Ship Dollar] - [Actual Ship Dollar]
    )
)

    Or if you're using a flat model without a separate date table:

    GSV $ BIAS (Plan vs Act) =
IF(
    HASONEVALUE('YourTable'[Period]),
    [Plan Ship Dollar] - [Actual Ship Dollar],
    SUMX(
        VALUES('YourTable'[Period]),
        [Plan Ship Dollar] - [Actual Ship Dollar]
    )
)

     

    For detailed information:

    Microsoft Learn Documentation – Understanding Totals in Power BI

    SQLBI – Fix Incorrect Totals in DAX with SUMX

    Radacad – Wrong Total in Matrix Visual in Power BI

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam

4 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi AzadHGS 

    Can you please try the below DAX?

    BIAS measures.

     

    BIAS :=
    SUMX (
    VALUES ( 'YourTable'[Column] ),
    [Plan] - [Actual]
    )

    -----------------------------------------------------------------

     

    BIAS % measure

    BIAS_Percentage_Corrected :=
    DIVIDE (
    SUMX (
    VALUES ( 'YourTable'[Column] ),
    [Plan] - [Actual]
    ),
    SUMX (
    VALUES ( 'YourTable'[Column] ),
    [Plan]
    )
    )

     

    If this answer your questions, kindly accept it as a solution and give kudos.

  • AzadHGS's avatar
    AzadHGS
    Regular Visitor
    1 year ago

    I tried it but it is not working still I am getting same values.

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    Hey AzadHGS ,

    The issue you’re encountering with incorrect totals in your matrix visual for the BIAS and BIAS % rows is a common DAX behavior. In Power BI matrix visuals:

    • Total rows are not the sum of individual values you see in the matrix.

    • Instead, DAX re-evaluates the measure at the total level using total Plan and total Actual, not summing each row's calculated result.

    That’s why you're seeing:

    • GSV $ BIAS (Plan vs Act) total as = 41,16,757 - 16,75,782 = 24,40,975
      instead of the expected sum of individual biases.

    Fix Totals Using DAX ISINSCOPE or HASONEVALUE

    You can customize your BIAS measures to show summed values in totals rather than default totals using ISINSCOPE() or HASONEVALUE().

    GSV $ BIAS (Plan vs Act) =
IF(
    ISINSCOPE('DateTable'[Period]),
    [Plan Ship Dollar] - [Actual Ship Dollar],
    SUMX(
        VALUES('DateTable'[Period]),
        [Plan Ship Dollar] - [Actual Ship Dollar]
    )
)

    Or if you're using a flat model without a separate date table:

    GSV $ BIAS (Plan vs Act) =
IF(
    HASONEVALUE('YourTable'[Period]),
    [Plan Ship Dollar] - [Actual Ship Dollar],
    SUMX(
        VALUES('YourTable'[Period]),
        [Plan Ship Dollar] - [Actual Ship Dollar]
    )
)

     

    For detailed information:

    Microsoft Learn Documentation – Understanding Totals in Power BI

    SQLBI – Fix Incorrect Totals in DAX with SUMX

    Radacad – Wrong Total in Matrix Visual in Power BI

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam