Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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))

     

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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))
  • Anonymous's avatar
    Anonymous
    3 years ago

    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)

parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

Anonymous collapsed version:

 

2 - Final Share Short % = 
//select current visible category group
VAR __currentCategory2 = SELECTEDVALUE ( Category[Category2] )
//create base table for the current visible category group
VAR __baseTable= 
ADDCOLUMNS (
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            FILTER ( 
                ALL ( Category ), 
                Category[Category2] = __currentCategory2  
            ), 
            Category[Category2], 
            Category[Category1] 
        ), 
        "@BaseShare", [1 - Base Share %],   --change this measure to the % measure
        "@RoundShare", ROUNDDOWN ( [1 - Base Share %], 2 ) 
    ),
    "@RemainderShare", [@BaseShare] - [@RoundShare] 
)
//get the count of remainder to be distributed
VAR __distributionCount = INT ( SUMX ( __baseTable, [@RemainderShare] * 100 ) )    
//find out to which categories the remainder will be distributed, in other words, what base % will be rounded upwards
VAR __distributionTable = 
    SELECTCOLUMNS ( 
        WINDOW ( 
            1, ABS, 
            __distributionCount, ABS, 
            __baseTable, 
            ORDERBY ( [@RemainderShare], DESC ) 
        ), 
        [Category1], 
        [Category2] 
    )
//round up the share %
VAR __roundUp = 
CALCULATE ( 
    ROUNDUP ( [Sum Value], 0 ), 
    KEEPFILTERS ( 
        TREATAS (  __distributionTable,  Category[Category1], Category[Category2] ) 
    ) 
)
//find result, the one which are not rounded up will be rounded down
RETURN COALESCE ( __roundUp, ROUNDDOWN ( [Sum Value], 0 ) )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Wow, that looks really amazing. I need to watch your videos about the WINDOW function to understand what you've done, that's going to be my next task on my list. Thanks a lot ! 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    parry2k I am trying to reproduce this but I am getting a bit lost... are you able to share the PBIX-file to which this measure belongs? Many thanks!