Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This one came out of this thread: https://community.powerbi.com/t5/Desktop/How-to-do-custom-rounding-in-DAX/m-p/2988669. Basically, how to do custom rounding such that:
This was the result.
Rounding =
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
eyJrIjoiOGYyMTRmZDUtMmNmOC00ZjdmLTg1NzYtMzczMjMyMDYzYjQyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9