How to do custom rounding in DAX?

Anonymous Interesting question. Does the remainder always get equally distributed to the top two contributors? 

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

Anonymous I did this (see below). PBIX is attached.

Measure = 
    VAR __Table = 
        GENERATE(
            SUMMARIZE(ALLSELECTED('Table'),'Table'[Category]),
                VAR __Value = [Percentage]
                VAR __RD = ROUNDDOWN(__Value,2)
                VAR __Decimal = __Value - __RD
            RETURN
                ROW(
                    "Value", __Value,
                    "RD", __RD,
                    "Decimal", __Decimal
                )
        )
    VAR __MaxDecimal = MAXX(__Table,[Decimal])
    VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category])
    VAR __Category = MAX('Table'[Category])
    VAR __Result = IF(__Category = __MaxCategory, ROUNDUP([Percentage],2), ROUNDDOWN([Percentage],2))
RETURN
    __Result

Here's a measure expression that shows one way to do it. You'll have to watch out for ties in the MOD values, and for when the values add up to >100 (but you can adapt this measure to do that).

AdjValue =
VAR vThisCategory =
    MIN ( T1[Category] )
VAR vThisVal = [AvgVal]
VAR vThisRD =
    ROUNDDOWN ( vThisVal, 0 )
VAR vThisMod =
    MOD ( vThisVal, 1 )
VAR tNew =
    ADDCOLUMNS ( ALL ( T1[Category] ), "cOrigVal", [AvgVal] )
VAR tRoundMod =
    ADDCOLUMNS (
        tNew,
        "cRD", ROUNDDOWN ( [cOrigVal], 0 ),
        "cMod", MOD ( [cOrigVal], 1 )
    )
VAR vGapTo100 =
    100 - SUMX ( tRoundMod, [cRD] )
VAR vModRank =
    RANKX ( SELECTCOLUMNS ( tRoundMod, "cMod2", [cMod] ), [cMod2], vThisMod, DESC )
VAR vResult =
    IF ( vModRank <= vGapTo100, ROUNDUP ( vThisVal, 0 ), ROUNDDOWN ( vThisVal, 0 ) )
RETURN
    vResult

Pat

Anonymous Ah! so remainder distribution is dynamic. 

Greg_Deckler ppm1 parry2k thanks for your calculations and input! However, I just realized I do'nt have one but two categories, which each can be filtered separately. The percentages of Category2 make up a total of 100%

So my table looks something like this:

Each Category2 rounded down this becomes:

Category2 X -> 2 missing percentage points

Category2 Y -> 2 missing percentage points

These missing percentage points need to be assigned to the 2 percentages with the highest decimal part. Please note there is a tie in X of Category2 so one percentage point goes to the first. I guess it must be fairly simple to add to the calculations you provided, but I can't manage to get it work... any ideas? Many thanks in advance!

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

hi Anonymous Sorry, I was traveling and was not able to get to you on the pbix file. Let me know if you still need the file. Cheers!!

Anonymous alright here it is, let me know if you have any questions. I might end up doing a video on it and will explain everything, not sure yet though.