- Microsoft Power BI Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish Community
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: How to do custom rounding in DAX?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to do custom rounding in DAX?

12-23-2022
07:50 AM

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?

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
07:19 AM

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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
09:35 AM

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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2022
02:21 AM

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)

30 REPLIES 30

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-08-2023
11:59 PM

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

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-08-2023
11:44 PM

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

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
05:05 PM

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

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-28-2022
05:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2022
02:24 AM

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 ! 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
05:02 PM

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

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-24-2022
02:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
07:19 AM

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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
08:18 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
08:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
09:35 AM

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

Latest book!:

DAX is easy, CALCULATE makes DAX hard...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-26-2022
02:21 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-24-2022
07:59 AM

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 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
02:33 AM

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 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
05:53 AM

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)

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
07:05 AM

@bolfri thanks for your suggestions, but I would like to avoid adding extra columns, because this will increase the model size and impact performance dramatically (the fact table has more than a million rows). In addition, the requirement is that the remaining percent points have to be added to the values with the largest decimal, in descending order (see my explanation above)...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
07:43 AM

You you want to change the value? If the Category 2 and 3 have same value, why do you want to give them differete percentage impact? 😄 You can do it in one step or by a measure. It was just an example of the results. If you can accept that (eg. this round up solution), I can give you Power Query M steps to change it in the source.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-25-2022
07:47 AM

@bolfriyour comment makes sense, but unfortunately I am not allowed to deviate from the requirements re. the distribution of the percent points. I am definitely curious to see what a Power Query M step for this would look like 🙂

Featured Topics

Top Solution Authors

User | Count |
---|---|

133 | |

59 | |

56 | |

55 | |

46 |

Top Kudoed Authors

User | Count |
---|---|

129 | |

74 | |

54 | |

53 | |

51 |