Forum Discussion
How to do custom rounding in DAX?
Anonymous I have modified things to be more in line with your data and requirements. See if this works. Updated PBIX is attached below signature.
Measure 2 = VAR __Cat2 = MAX('Table'[Category2]) VAR __Table = GENERATE( SUMMARIZE( FILTER(ALLSELECTED('Table'), [Category2] = __Cat2), 'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])), VAR __Value = [Value] VAR __RD = ROUNDDOWN(__Value,0) VAR __Decimal = __Value - __RD RETURN ROW( "RD", __RD, "Decimal", __Decimal ) ) VAR __MaxDecimal = MAXX(__Table,[Decimal]) VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1]) VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal]) VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1]) VAR __Category = MAX('Table'[Category1]) VAR __Result = IF( __Category = __MaxCategory || __Category = __2ndMaxCategory, ROUNDUP(MAX([Percentage]),0), ROUNDDOWN(MAX([Percentage]),0) ) RETURN __Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))Anonymous Easy fix for that, see below and attached PBIX.
Measure 2 = VAR __Cat2 = MAX('Table'[Category2]) VAR __Table = GENERATE( SUMMARIZE( FILTER(ALLSELECTED('Table'), [Category2] = __Cat2), 'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])), VAR __Value = [Value] VAR __RD = ROUNDDOWN(__Value,0) VAR __Decimal = __Value - __RD RETURN ROW( "RD", __RD, "Decimal", __Decimal ) ) VAR __MaxDecimal = MAXX(__Table,[Decimal]) VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1]) VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal]) VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1]) VAR __Category = MAX('Table'[Category1]) VAR __SumDown = SUMX(__Table, [RD]) VAR __Result = SWITCH(__SumDown, 99, IF( __Category = __MaxCategory, ROUNDUP(MAX([Percentage]),0), ROUNDDOWN(MAX([Percentage]),0) ), 98, IF( __Category = __MaxCategory || __Category = __2ndMaxCategory, ROUNDUP(MAX([Percentage]),0), ROUNDDOWN(MAX([Percentage]),0) ) ) RETURN __Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))Great, that worked! Thanks so much, very much appreciated 🙂
And I added an alternative for SWITCH in case nothing needs to be rounded (in the unlikely case all are numbers with 0 decimals)
Greg_Deckler ppm1 parry2k thanks for your calculations and input! However, I just realized I do'nt have one but two categories, which each can be filtered separately. The percentages of Category2 make up a total of 100%
So my table looks something like this:
| Category1 | Category2 | Percentage |
| A | X | 43.75 |
| B | X | 12.50 |
| C | X | 12.50 |
| D | X | 31.25 |
| A | Y | 40.38 |
| B | Y | 5.77 |
| C | Y | 23.08 |
| D | Y | 30.77 |
Each Category2 rounded down this becomes:
| Category1 | Category2 | Percentage |
| A | X | 43 |
| B | X | 12 |
| C | X | 12 |
| D | X | 31 |
| A | Y | 40 |
| B | Y | 5 |
| C | Y | 23 |
| D | Y | 30 |
Category2 X -> 2 missing percentage points
Category2 Y -> 2 missing percentage points
These missing percentage points need to be assigned to the 2 percentages with the highest decimal part. Please note there is a tie in X of Category2 so one percentage point goes to the first. I guess it must be fairly simple to add to the calculations you provided, but I can't manage to get it work... any ideas? Many thanks in advance!
| Category1 | Category2 | Rounded percentage |
| A | X | 44 |
| B | X | 13 |
| C | X | 12 |
| D | X | 31 |
| A | Y | 40 |
| B | Y | 6 |
| C | Y | 23 |
| D | Y | 31 |
Community ChampionAnonymous I have modified things to be more in line with your data and requirements. See if this works. Updated PBIX is attached below signature.
Measure 2 =
VAR __Cat2 = MAX('Table'[Category2])
VAR __Table =
GENERATE(
SUMMARIZE(
FILTER(ALLSELECTED('Table'), [Category2] = __Cat2),
'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])),
VAR __Value = [Value]
VAR __RD = ROUNDDOWN(__Value,0)
VAR __Decimal = __Value - __RD
RETURN
ROW(
"RD", __RD,
"Decimal", __Decimal
)
)
VAR __MaxDecimal = MAXX(__Table,[Decimal])
VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1])
VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal])
VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1])
VAR __Category = MAX('Table'[Category1])
VAR __Result =
IF(
__Category = __MaxCategory || __Category = __2ndMaxCategory,
ROUNDUP(MAX([Percentage]),0),
ROUNDDOWN(MAX([Percentage]),0)
)
RETURN
__Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))
Brilliant, it works, also in my 'real' dataset! Thanks a lot Greg_Deckler , you're a star! 🙂
And also thanks to all other contributors bolfri ppm1 parry2k , you helped me shape my thoughts.
Greg_Deckler oops I noticed an error - it looks like the result now always rounds up two values, even if only rounding up one value should have been done, see example below for category Z... which should have been 50 + 24 + 16 + 10. Any thoughts on this?
- Greg_Deckler
Anonymous Easy fix for that, see below and attached PBIX.
Measure 2 = VAR __Cat2 = MAX('Table'[Category2]) VAR __Table = GENERATE( SUMMARIZE( FILTER(ALLSELECTED('Table'), [Category2] = __Cat2), 'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])), VAR __Value = [Value] VAR __RD = ROUNDDOWN(__Value,0) VAR __Decimal = __Value - __RD RETURN ROW( "RD", __RD, "Decimal", __Decimal ) ) VAR __MaxDecimal = MAXX(__Table,[Decimal]) VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1]) VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal]) VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1]) VAR __Category = MAX('Table'[Category1]) VAR __SumDown = SUMX(__Table, [RD]) VAR __Result = SWITCH(__SumDown, 99, IF( __Category = __MaxCategory, ROUNDUP(MAX([Percentage]),0), ROUNDDOWN(MAX([Percentage]),0) ), 98, IF( __Category = __MaxCategory || __Category = __2ndMaxCategory, ROUNDUP(MAX([Percentage]),0), ROUNDDOWN(MAX([Percentage]),0) ) ) RETURN __Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))