Forum Discussion

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

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

Rounding.pbix20 KB

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

Rounding.pbix20 KB

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)

Anonymous

Not applicable

3 years ago

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:

Category1	Category2	Percentage
A	X	43.75
B	X	12.50
C	X	12.50
D	X	31.25
A	Y	40.38
B	Y	5.77
C	Y	23.08
D	Y	30.77

Each Category2 rounded down this becomes:

Category1	Category2	Percentage
A	X	43
B	X	12
C	X	12
D	X	31
A	Y	40
B	Y	5
C	Y	23
D	Y	30

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!

Category1	Category2	Rounded percentage
A	X	44
B	X	13
C	X	12
D	X	31
A	Y	40
B	Y	6
C	Y	23
D	Y	31

ppm1

Solution Sage

3 years ago

I see you worked a tie into the example data. Please see this updated measure expression that uses RAND to break the tie.

AdjValue = 
VAR vThisVal = [AvgVal]
VAR vThisCategory1 =
    MIN ( T1[Category1] )
VAR tNew =
    ADDCOLUMNS (
        CALCULATETABLE ( DISTINCT ( T1[Category1] ), REMOVEFILTERS ( T1[Category1] ) ),
        "cOrigVal", [AvgVal]
    )
VAR tRoundMod =
    ADDCOLUMNS (
        tNew,
        "cRD", ROUNDDOWN ( [cOrigVal], 0 ),
        "cMod", MOD ( [cOrigVal], 1 ),
        "cRand", RAND()/1000
    )
VAR vThisModRand = SUMX(FILTER(tRoundMod, T1[Category1] = vThisCategory1), [cMod] + [cRand])
VAR vGapTo100 =
    100 - SUMX ( tRoundMod, [cRD] )
VAR vModRank =
    RANKX ( tRoundMod, [cMod] + [cRand], vThisModRand, DESC )
VAR vResult =
    IF ( vModRank <= vGapTo100, ROUNDUP ( vThisVal, 0 ), ROUNDDOWN ( vThisVal, 0 ) )
RETURN
    vResult

Pat

Anonymous
Not applicable
3 years ago
Hi Pat ppm1 I tested this on my simple test dataset, and noticed the results are correct! However, it seems that the values assigned to the ties (B and C in Category1) are very instable, i.e. when I refresh the report the values change every time, see screenshots below in column TEST. Maybe this has something to do with the results stored in a virtual table?

After refresh:

bolfri
Solution Sage
3 years ago
Hi,

I think you're trying to do it wrong. Do not create a too complex measures that's not nessesery. 😄

In Power Query M:
Add a Percentage_to_number column which is your oryginal Percentage divided by 100 and make this as a number (with decimal places)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYhNjPXNTpVidaCUnqIihkZ6pAVjEGUPEBSpibKhnBNEFMicSZI6BnrEF3ByQiKmeuTncGJCAkbGegQXcGJCIsQFYTSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category1 = _t, Category2 = _t, Percentage = _t]), #"Replaced Value" = Table.ReplaceValue(Source,".",",",Replacer.ReplaceText,{"Percentage"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Category1", type text}, {"Category2", type text}, {"Percentage", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Percentage_to_number", each [Percentage] / 100), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage_to_number", type number}}) in #"Changed Type1"

In DAX:
Create a Percentage mesure and change the format to Percentage:
Percentage measure = SUM('Sample'[Percentage_to_number])
As you can see it's almost the same as previous.
To get rid of decimal places simply change it to 0.

Final efect:

The numbers are correct and they are calculated by mathematic law 🙂
- Anonymous
  Not applicable
  3 years ago
  Many thanks! This would have been a great solution... but look at the results of the Percentage measure of Category X... 44 + 13 + 13 + 31 = 101 !
  The tie (and I think any two values with ,5 decimal in one category) seems to mess up the total... hence the need for custom rounding. Let me know your thoughts on how to resolve this, thanks 🙂
  - bolfri
    Solution Sage
    3 years ago
    I see your point. You said that you want rounding down, so...
    
    On your oryginal data add a new DAX column:
    Rounding = ROUNDDOWN('Sample'[Percentage],0)
    
    Then add a new column
    New Percentage =
    var numerator= [Rounding]
    var denominator= CALCULATE(SUM('Sample'[Rounding]),ALLEXCEPT('Sample','Sample'[Category2]))
    return DIVIDE(numerator,denominator)
    
    Result in table:
    
    As you can see the missing 2% were divided between Category A and D.
    But we can use RoundingUp to give this missing 2% to category B and C.
    
    Again. Add a new Column:
    RoundingUp = ROUNDUP([Percentage],0)
    
    And another new column:
    New Percentage UP =
    var numerator= [RoundingUp]
    var denominator= CALCULATE(SUM('Sample'[RoundingUp]),ALLEXCEPT('Sample','Sample'[Category2]))
    return DIVIDE(numerator,denominator)
    
    Result in table:
    
    Based on the path you'll choose you will get different results.
    
    Choose the one you prefer.

Greg_Deckler

Community Champion

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

Rounding.pbix20 KB

Anonymous
Not applicable
3 years ago
Brilliant, it works, also in my 'real' dataset! Thanks a lot Greg_Deckler , you're a star! 🙂
And also thanks to all other contributors bolfri ppm1 parry2k , you helped me shape my thoughts.
- Anonymous
  Not applicable
  3 years ago
  Greg_Deckler oops I noticed an error - it looks like the result now always rounds up two values, even if only rounding up one value should have been done, see example below for category Z... which should have been 50 + 24 + 16 + 10. Any thoughts on this?

Forum Discussion

How to do custom rounding in DAX?

Related content

Rounding

Custom rounding

DAX - cumulative % small difference. Rounding?

New card visual with rounded corners

Round and divide DAX formula