Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PunchBird
Helper V
Helper V

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, with the following custom rounding:

- All percentages need to be rounded down to the nearest integer.

- Then it needs to be identified how much percentage points are missing to reach the 100% in total.

- Next, these percentage points need to be added each to the percentages with the largest decimal part in descending order.

Please note the dataset is huge so the impact of the DAX calculation should be limited as much as possible

 

So for example:

Category A: 26.98%,
Category B: 34.78%,
Category C: 38.24%

 

Rounded down this becomes:

Category A: 26
Category B: 34
Category C: 38
26 + 34 + 38 = 98 -> 2 missing percentage points

 

These missing percentage points need to be assigned to the 2 percentages with the highest decimal part:
Category A: 27
Category B: 35
Category C: 38
27 + 35 + 38 = 100%

 

How can I do this with DAX? I think some iteration and /or buffering is required, but how do I do this?

@parry2k maybe you know?

3 ACCEPTED SOLUTIONS

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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)

View solution in original post

30 REPLIES 30

I can prepare Power Query M for rounddown or roundup solution, but without this additional req to split this % value between Category 2 or 3. Sorry. 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

ppm1_0-1671893166434.png

 

Pat

Microsoft Employee

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?

PunchBird_4-1671981426544.png

 

After refresh:

PunchBird_0-1671981109941.png

PunchBird_2-1671981182183.png

PunchBird_3-1671981198177.png

 

parry2k
Super User
Super User

@PunchBird Ah! so remainder distribution is dynamic. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

yes indeed!

 

ppm1
Solution Sage
Solution Sage

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

ppm1_0-1671815036664.png

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

Microsoft Employee
Greg_Deckler
Super User
Super User

@PunchBird 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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler thanks for your calculation. I tested this on my testdata (see below for more elaborate example), but apparently your calculation only works when there is one percent point to be added. If there are two percent points that need to be added, the total is 99%, see also screenshot below. Maybe I could use a for- or while loop, like you described here?

PunchBird_0-1671975601952.png

 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k the remainder needs to be distributed in descending order. So if the remainder is 1, it is assigned to the percentage that had largest decimal part, and  if the remainder is 2, it is assigned to the two top percentages that had largest decimal part

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.