The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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