Why total is wrong even if there is no filter applied?

[Anonymous]

Why is the total is different? The correct total should be 57,722.9. There is no filter. 

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to make the total row display the correct value, you can try this measure to replace the measure [Export min $]:

Export min $_new =

var _new=SUMMARIZE('Table','Table'[Section code],"_value",[Export min $])

return IF(HASONEVALUE('Table'[Section code]),[ Export min $],SUMX(_new,[_value]))

Replace the table name and column name depending on the circumstances. And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

@Anonymous , Try a measure like

sumx(value(Table[Section]), [Export Min])

[negi007]

@Anonymous  I hope you are not using fixed decimal in the powerbi instead of decimal number. Fixed decmial will keep the data till the decimal places you specify and your calculation will get impacted. Please change the data type to decimal number.

Read below for more detailed on this

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

Number types

Power BI Desktop supports three number types:

Decimal Number – Represents a 64 bit (eight-byte) floating point number. It’s the most common number type and corresponds to numbers as you usually think of them. Although designed to handle numbers with fractional values, it also handles whole numbers. The Decimal Number type can handle negative values from -1.79E +308 through -2.23E -308, 0, and positive values from 2.23E -308 through 1.79E + 308. For example, numbers like 34, 34.01, and 34.000367063 are valid decimal numbers. The largest precision that can be represented in a Decimal Number type is 15 digits long. The decimal separator can occur anywhere in the number. The Decimal Number type corresponds to how Excel stores its numbers.

Fixed Decimal Number – Has a fixed location for the decimal separator. The decimal separator always has four digits to its right and allows for 19 digits of significance. The largest value it can represent is 922,337,203,685,477.5807 (positive or negative). The Fixed Decimal Number type is useful in cases where rounding might introduce errors. When you work with many numbers that have small fractional values, they can sometimes accumulate and force a number to be slightly off. Since the values past the four digits to the right of decimal separator are truncated, the Fixed Decimal type can help you avoid these kinds of errors. If you’re familiar with SQL Server, this data type corresponds to SQL Server’s Decimal (19,4), or the Currency Data type in Power Pivot.

Whole Number – Represents a 64 bit (eight-byte) integer value. Because it’s an integer, it has no digits to the right of the decimal place. It allows for 19 digits; positive or negative whole numbers between -9,223,372,036,854,775,807 (-2^63+1) and 9,223,372,036,854,775,806 (2^63-2). It can represent the largest possible precision of the various numeric data types. As with the Fixed Decimal type, the Whole Number type can be useful in cases where you need to control rounding.

[NamishB]

Hi @Anonymous - I think the total should be 58064.7 (As shown in excel)

In your Power BI, can you try removing all other columns except 'Export Min $' and enable total from the format tab? This should match the excel result.

Try adding more columns and see where is it going wrong? 

If you are using a measure for total- make sure there are no filters in dax calculation or any calculations been messed up.

Can you share your dax formula if unsure.

Hope this helps give some direction in a right way 

Cheers,

-Namish B 

[FarhanAhmed]

What is the aggregation behind Export Min $ in Power BI ? is it measure ?