Forum Discussion
How to do custom rounding in DAX?
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))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))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)
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 |
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 🙂
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 🙂
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 columnNew 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.
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)...