Totals Not adding up correctly

[Anonymous]

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
Total	8.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

[Syndicate_Admin · Accepted solution]

@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😀

[Ashish_Mathur]

Hi,

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

[Anonymous]

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 1	9221346.25
Company 2	155833.69
Company 3	-36408.92
Company 4	-999.97
Company 5	-14441.8
Compnay 6	0
Company 7	101983.53
Company 8	437385.98
Company 9	0
Company 10	3967.72

[Ashish_Mathur]

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

[PowerBIET]

practice2.pbix

[Syndicate_Admin]

@Syndicate_Admin
Taking the data from the table loaded in power bi, the calculations are correct in the power bi.