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 you already have an excellent solution from Greg_Deckler I wanted to try new WINDOW functions and see if that will help. Keep in mind, it is based on that you have a category dimension table that has a relationship with the transaction table.

here is the DAX measure, some of the steps can be collapsed into one step but I just added multiple variables for clarity and to explain the logic behind the solution. ( I will post a collapsed version soon)

2 - Final Share % = 
//select current visible category group
VAR __currentCategory2 = SELECTEDVALUE ( Category[Category2] )
//create base table for the current visible category group
VAR __baseTable= 
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            FILTER ( 
                ALL ( Category ), 
                Category[Category2] = __currentCategory2  
            ), 
            Category[Category2], 
            Category[Category1] 
        ), 
        "@BaseShare", [1 - Base Share %]   --change this measure to the % measure
    )
//add a column to round down base %
VAR __roundTable = 
    ADDCOLUMNS ( 
        __baseTable, 
        "@RoundShare", ROUNDDOWN ( [@BaseShare], 2 ) 
    )
//add a column to difference between base share and round down share %
VAR __remainderTable = 
    ADDCOLUMNS (
        __roundTable,
         "@RemainderShare", [@BaseShare] - [@RoundShare] 
    )
//get the count of remainder to be distributed
VAR __distributionCount = INT ( SUMX ( __remainderTable, [@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, 
            __remainderTable, 
            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 IF ( __roundUp == BLANK (), ROUNDDOWN ( [Sum Value], 0 ), __roundUp )


Also, if interested check out the full playlist on my youtube channel for new WINDOW DAX functions. https://youtube.com/playlist?list=PLiYSIjh4cEx0BDzmo48YIPzw_dIC0Kd95