Forum Discussion

M34Viby's avatar
M34Viby
New Member
1 year ago
Solved

wrong grand total

Anyone able to help me out on the below issue? I am using if, average, count and sum in this below statement. The line totals are correct, but the grand total is not.

 

Outperformance CSSUPR = IF(AVERAGE('CDBL Performance CSSUPR'[AdjTCE])-AVERAGE('CDBL Performance CSSUPR'[NBFV])>0, (AVERAGE('CDBL Performance CSSUPR'[AdjTCE])-AVERAGE('CDBL Performance CSSUPR'[NBFV]))*(COUNT('CDBL Performance CSSUPR'[Days CSSUPR])-SUM('CBS off-hire CSSUPR'[OffHireDays])),0)
 
I have also tried to add sumx and values like this, but the line totals are then also wrong.
Outperformance CSSUPR test = SUMX(VALUES('CBS off-hire CSHDY'[Vessel Name]),IF(AVERAGE('CDBL Performance CSSUPR'[AdjTCE])-AVERAGE('CDBL Performance CSSUPR'[NBFV])>0, (AVERAGE('CDBL Performance CSSUPR'[AdjTCE])-AVERAGE('CDBL Performance CSSUPR'[NBFV]))*(COUNT('CDBL Performance CSSUPR'[Days CSSUPR])-SUM('CBS off-hire CSSUPR'[OffHireDays])),0))
 
Thanks in advance
 
Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago
    Outperformance CSSUPR = 
    SUMX(
    VALUES('CBS off-hire CSSUPR'[Vessel Name]),
    IF(
    AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[AdjTCE]) 
    - AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[NBFV]) > 0,
    (AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[AdjTCE])
    - AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[NBFV]))
    * (COUNTX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[Days CSSUPR]) 
    - SUMX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CBS off-hire CSSUPR'[OffHireDays])),
    0
    )
    )

    If needed, further adjustments can be made depending on how your data is structured.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi M34Viby ,

    In general, the usual solution to the Total error problem is to add another MEASURE: 

    SUMX(VALUES(your column header field/row header field), [your measure])

    You can refer to the solution in this thread below:
    Solved: Totals in Matrix - Microsoft Fabric Community

    But please note that with only DAX without any data it is almost impossible for us to help you find a solution, could you please provide us with your sample data and explain how your visual object was created and what fields were placed in the rows, columns and values respectively, please!

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

2 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago
    Outperformance CSSUPR = 
    SUMX(
    VALUES('CBS off-hire CSSUPR'[Vessel Name]),
    IF(
    AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[AdjTCE]) 
    - AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[NBFV]) > 0,
    (AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[AdjTCE])
    - AVERAGEX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[NBFV]))
    * (COUNTX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CDBL Performance CSSUPR'[Days CSSUPR]) 
    - SUMX(VALUES('CBS off-hire CSSUPR'[Vessel Name]), 'CBS off-hire CSSUPR'[OffHireDays])),
    0
    )
    )

    If needed, further adjustments can be made depending on how your data is structured.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi M34Viby ,

    In general, the usual solution to the Total error problem is to add another MEASURE: 

    SUMX(VALUES(your column header field/row header field), [your measure])

    You can refer to the solution in this thread below:
    Solved: Totals in Matrix - Microsoft Fabric Community

    But please note that with only DAX without any data it is almost impossible for us to help you find a solution, could you please provide us with your sample data and explain how your visual object was created and what fields were placed in the rows, columns and values respectively, please!

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