Forum Discussion
Anonymous
3 years agoNot applicable
How to do custom rounding in DAX?
Hi all, Here is a Christmas brain cracker for our DAX specialists! I want to create a DAX measure that calculates the percentage of values per category in a table to the total of that table, wit...
Greg_Deckler
Community Champion
3 years agoAnonymous 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))Anonymous
3 years agoNot applicable
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)