Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Totals Not adding up correctly

My Power BI table is adding up to wrong total at $9.90.  The correct totals should be 8.795, based same data in excel. I am formatted a basic Revenue = SUM(Table[Revenue) measure.  Please look my measure, to see why it is not totaling correctly. 
 
Excel
 9.200
 0.156
 (0.360)
 (1.000)
 (0.140)
 0.102
 0.437
 0.400
Total8.795
 
Power BI Table
 

 

 
Revenue Formatted =
VAR _salesMeasure =
    SUMX ( VALUES ( Table[Company]  ), [Revenue] )
VAR _result =
    SWITCH (
        TRUE (),
        ABS ( _salesMeasure ) >= 1000000, FORMAT(ROUND(DIVIDE(_salesMeasure,1000000,0),1),"$0.00;$0.0##"),
        ABS ( _salesMeasure ) >= 100000,FORMAT(ROUND( DIVIDE(_salesMeasure,1000000,0),3),"$#0.000"),
        ABS ( _salesMeasure ) >= 10000,  FORMAT(DIVIDE ( _salesMeasure, 100000, 0 ),"$0.00;($0.00)"),
        ABS ( _salesMeasure ) >= 1000,  FORMAT(DIVIDE ( _salesMeasure, 10000, 0 ),"$0.00;($0.00)"),
     ABS ( _salesMeasure ) >= 100,  FORMAT(DIVIDE ( _salesMeasure, 1000, 0 ),"$0.00;($0.000)"),

        ABS ( _salesMeasure ) > 0, DIVIDE(  _salesMeasure, 100 )

    )
RETURN
    _result




Need Help
  • Syndicate_Admin's avatar
    Syndicate_Admin
    1 year ago

    @Syndicate_Admin
    If you can share the PBIX, it is much more expeditious to validate and solve your requirement
    Please try the following formula. 

    SumTotOk :=
SUMX(
    // ------PLEASE CHANGE Table[Company] WITH THE COLUMN ITERATED AT THE VISUAL----------
    VALUES( Table[Company] ),
    CALCULATE(
        SUMX(
            Table,
            [Revenue]
        ),
        ALLSELECTED()
    )
)

    I hope it is helpful, if so please accept the solution. Kudos are welcome😀

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share the download link of the PBI file and show the problem there clearly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Here is the sample data and problem measure (Formatted) I used.  WIl this work? I can't link to the PBIX

       

      .1-60 Days = SUM('Table'[1-60 Days])
       
      .Formatted =
      VAR _salesMeasure =
          SUMX ( VALUES ( 'Table'[Company]  ), [.1-60 Days] )
      VAR _result =
          SWITCH (
              TRUE (),
              ABS ( _salesMeasure ) >= 1000000, FORMAT(ROUND(DIVIDE(_salesMeasure,1000000,0),1),"$0.00;$0.0##"),
              ABS ( _salesMeasure ) >= 100000,FORMAT(ROUND( DIVIDE(_salesMeasure,1000000,0),3),"$#0.000"),
              ABS ( _salesMeasure ) >= 10000,  FORMAT(DIVIDE ( _salesMeasure, 100000, 0 ),"$0.00;($0.00)"),
              ABS ( _salesMeasure ) >= 1000,  FORMAT(DIVIDE ( _salesMeasure, 10000, 0 ),"$0.00;($0.00)"),
           ABS ( _salesMeasure ) >= 100,  FORMAT(DIVIDE ( _salesMeasure, 1000, 0 ),"$0.00;($0.000)"),
              ABS ( _salesMeasure ) > 0, DIVIDE(  _salesMeasure, 100 )
          )
      RETURN
          _result
       
      Data Below

      Company1-60 Days61-90 

      Company 19221346.25
      Company 2155833.69
      Company 3-36408.92
      Company 4-999.97
      Company 5-14441.8
      Compnay 60
      Company 7101983.53
      Company 8437385.98
      Company 90
      Company 103967.72

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        1 year ago

        Looks like a rounding error.  Share the download link of the PBI file.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    1 year ago

    @Syndicate_Admin
    If you can share the PBIX, it is much more expeditious to validate and solve your requirement
    Please try the following formula. 

    SumTotOk :=
SUMX(
    // ------PLEASE CHANGE Table[Company] WITH THE COLUMN ITERATED AT THE VISUAL----------
    VALUES( Table[Company] ),
    CALCULATE(
        SUMX(
            Table,
            [Revenue]
        ),
        ALLSELECTED()
    )
)

    I hope it is helpful, if so please accept the solution. Kudos are welcome😀