Rounding down of Decimals in case of Ties

[anwarbi]

Hi, 

Can anyone please help me to fix the rounding of decimal value which is an average value resulting from a measure that i have created.

E.g.

In cases where the average value is 7.250 (a tie situation), when looked at single decimal, the result I am getting in power bi report is 7.3, however i want to have 7.2 as a result. (rounding up in case of ties).

And in other cases where the average value is 7.260 then it should be 7.3.

Appreciate your help.  @amitchandak 

[parry2k]

@anwarbi yes it will not work for measure, you need to use DAX function ROUND ROUND function (DAX) - DAX | Microsoft Docs

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[anwarbi]

Not really sure, How round can be applies as it will be one directional Round up or down. 

I am looking to round down only incase where the value is tied.

[rsbin]

@anwarbi ,

DAX has ROUNDUP and ROUNDOWN functions.  With these you can then specify how you want your Measure to be Rounded.

Regards,

[anwarbi]

I want to conditonally round down tie cases where the value is e.g. 1.25 so the result should be 1.2

but where the value is 1.26, I want them to round up to 1.3

[rsbin]

@anwarbi ,

Are you able to share the Measure you are working with?  It would better help me to structure your Conditional Rounding.

[anwarbi]

I want the conditional round function to apply on the below measure. 

Average result= CALCULATE(AVERAGEX(VALUES('Country'[Company_names]),CALCULATE(AVERAGE('Table1'[points]),'Table1'[related companies]<>"",'Table1'[mapping]<>"")),ALL('Table1'[Company]))

[rsbin · Accepted solution]

@anwarbi ,

In DAX, I prefer using the SWITCH Statement.  Similar to IF, but I find it cleaner and easier.

Average result Rounded = SWITCH(
                             TRUE(),
                            ( [Average result]*10 - TRUNC( [Average result]*10, 0 )) <= .5, ROUNDDOWN(  [Average result], 1 ),
                            ROUNDUP( [Average result], 1 ))

Average result   Average result Rounded

7.25	7.2
7.26	7.3
1.25	1.2
1.26	1.3

Hope you are able to get this to work for you.

Regards,

[parry2k]

@anwarbi check this and apply as you see fit. Number.Round - PowerQuery M | Microsoft Docs

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[anwarbi]

I have to apply this on measure that I have created which calculates average value. I am not sure, how this can be applied to a measure?