Rounding Issues

[tyleharris]

Hi all,

I work in the beverage industry and I'm working on a project that requires me to create a measure called "Net 9L Cases". It's an industry standard of volume that simply takes the units sold, multiples it by the metric volume, then divides by 9000 mL. If it's a liter it requires an additional step of taking converting it to mL. Below are the Gross and Return measures:

9L Cases (Gross) =

IF(SELECTEDVALUE(Items[Alt Liquid Volume UOM]) = "L",

    ROUND(CALCULATE(SUMX('Sales Transactions',('Sales Transactions'[Sales Quantity Gross])*RELATED(Items[Alt Liquid Volume])*1000)/9000),6),

    ROUND(CALCULATE(SUMX('Sales Transactions', ('Sales Transactions'[Sales Quantity Gross])*RELATED(Items[Alt Liquid Volume]))/9000),6)

)

9L Cases (Return) =
IF(SELECTEDVALUE(Items[Alt Liquid Volume UOM]) = "L",

    ROUND(CALCULATE(SUMX('Sales Transactions',('Sales Transactions'[Sales Quantity Return])*RELATED(Items[Alt Liquid Volume])*1000)/9000),6),

    ROUND(CALCULATE(SUMX('Sales Transactions', ('Sales Transactions'[Sales Quantity Return])*RELATED(Items[Alt Liquid Volume]))/9000),6)

)

Then the Net Measure uses the two above:

9L Case Net =
IF(COUNTROWS(VALUES(Items[Item Identifier])) = 1,

    CALCULATE([9L Cases (Gross)]-[9L Cases (Return)]),

    SUMX(VALUES(Items[Item Identifier]), CALCULATE([9L Cases (Gross)]-[9L Cases (Return)])

))

The measures all work as intended and return the values I want, but I'm having issues with the decimals and rounding them. Due to the measure dividing by 9000 its causing some long decimal values. When the dataset is small, it is pretty accurate, but when I start looking at a years worth of sales, the grand total is off by quite a bit. Currently, I have the measures set at rounding to 6. This produces the most accurate values in the line context that actually equals the grand total.See below:



I'd like the values to round to the second decimal place. However, when I change the measure to round(value,2) it changes the row's values to the correct rounded values, but the grand total still returns a grand total as if it's summing all the values with their long decimal values (off by a little though). See Below:


As you see the values have been rounded to the second decimal place like I wanted, but the grand total isstill summing the values as if the string of decimals is still there. The Grand Total should be 3,877,577.07 when rounding to the 2nd decimal place.

I unfortunately cannot post a link to my data set as I am blocked from using file sharing tools outside of my agency, but I'd be happy to email to you if need be.

Thank you!

[jthomson]

So if you've got two values that are both 0.495 and you're rounding to two decimal places, you want the total row to show 1 and not 0.99, right? Interesting choice. It might be best to:

a) clean up your data so that everything in your data set is using either millilitres or litres, it'd make everything easier and you don't need to use a bunch of if statements. It'd be one line of code in Power Query

b) use calculated columns rather than measures to work out the values for each row, and then sum those new columns

[tyleharris]

@jthomson 

Now that you put it that way, I'm not so sure haha.  Is there a way to simply truncate the decimals to the 2nd decimal place? I know this can be done in excel, but the DAX function Trunc() returns intergers.

Also, everything is currently in millileters. The measure just looks to see if it's a Liter and if it is, it converts to millieters before doing the rest of the calculation. I'm also in a live connection, so I can't create caluated columns without taking the data out and putting it back in. I'm hoping to solve it in my native live connection, that is, if it's possible.

Thank you!

[Icey · Accepted solution]

Hi @tyleharris ,

Try this:

Measure =
IF (
    HASONEVALUE ( 'Table'[Column1] ),
    ROUND ( MAX ( 'Table'[Column2] ), 2 ),
    SUMX ( 'Table', ROUND ( MAX ( 'Table '[Column2] ), 2 ) )
)

Best Regards,

Icey

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