topic Re: MROUND - Rounding to the nearest 5 in DAX Commands and Tips

MROUND - Rounding to the nearest 5

Suzie_Suze_Sue — Wed, 11 Jan 2023 13:09:56 GMT

Good afternoon,

Having an issue with MROUND.

I have a table with college courses, and would like to look at the FTE for each institution and course, how ever it doesn't seem to like "the value for FTE cannot be determined. Either the column doesn't exist, or there is not current row for this column"

So table would look something like this

Year	Institution	Qual	FTE	Gender
2019/20	1	Dip	1	1
2020/21	3	Cert	0.5	2
2019/20	1	First	1	2
2020/21	3	Cert	1	1
2019/20	1	First	1	1

The measure was FTE Rounded = MROUND([FTE]),5)

So the output would look like

Year FTE FTE Rounded

2019/20 3 5

2020/21 1.5 0 (as less than 2.5, rounds down)

Any help greatly recieved.

Re: MROUND - Rounding to the nearest 5

amitchandak — Wed, 11 Jan 2023 15:51:53 GMT

Suzie_Suze_Sue , this you need to create a new column

FTE Rounded = MROUND([FTE]),5)

The measure will be like

FTE Rounded =Sumx(Table, MROUND([FTE]),5) )

Re: MROUND - Rounding to the nearest 5

FreemanZ — Wed, 11 Jan 2023 16:04:38 GMT

hi Suzie_Suze_Sue

not sure if i fully get your, please create a calculated table with this:

SumTable = VAR _table = ADDCOLUMNS( VALUES( TableName[Year] ), "FTE", CALCULATE(SUM(TableName[FTE])) ) RETURN ADDCOLUMNS( _table, "FTE Rounded", MROUND([FTE], 5) )

i tried and it worked like this:

Re: MROUND - Rounding to the nearest 5

Suzie_Suze_Sue1 — Fri, 13 Jan 2023 12:21:50 GMT

Thanks

I have tried this and get the error

The syntaxt for ') is incorrect. 9DAX(Sumx(Table, MROUND([FTE]),5) ))).

Re: MROUND - Rounding to the nearest 5

Suzie_Suze_Sue1 — Mon, 16 Jan 2023 15:23:02 GMT

Thanks this works in a table, but doesn't work when adding in a slicer.

Say for example I needed a matrix in Gender by year with a larger dataset, and then want to filer by qualification

Filter = Dip

16/17 17/18 18/19 19/20 20/21

Male 222 (220) 256(255) 212(210) 248(250) 227(225)

Female 229 (230) 246(245) 232(230) 248(250) 207(205)

Would I need to add multiple columns in? In brackets is the rounded numbers, don't need to to displayed this way, but this is what should be pulling through rather than the whole number.

Thank you very much

Re: MROUND - Rounding to the nearest 5

FreemanZ — Tue, 17 Jan 2023 06:11:06 GMT

hi Suzie_Suze_Sue1

you would need a measure like this:

measure = VAR _sum = SUM(TableName[FTE]) RETURN MROUND(_sum, 5)

with the sample data you provided in the original post and changing 5 to 2, it worked like this:

p.s. please consider @someone, if you seek further suggestions.

Re: MROUND - Rounding to the nearest 5

Suzie_Suze_Sue1 — Tue, 17 Jan 2023 11:15:17 GMT

Perfect!! Thank you

Re: MROUND - Rounding to the nearest 5

johnyip — Wed, 01 Feb 2023 08:43:07 GMT

Try this:

FTE Rounded =Sumx(Table, MROUND([FTE],5) )