Total Percentage per Category (Count) off when rounded to whole number

[Anonymous]

Hello,

I have the following graph which I've created to calculated to Count each sub-category's percentage total:

The above is fine, I can see that each category totals up to 100%. However, I have 2 Decimal Places.

However, I want to show without decimal places. Once I remove the decimal place, it seems due to rounding - some of the values do not total to 100% - with the below highlighted ones being slightly over 100%.

Is there any way to show the these percentages without decimal places and still total up to 100%?

[leahschneider]

If anyone is interested I solved with my own data using this measure.

Measure =

    VAR __Table =

        GENERATE(

            SUMMARIZE(ALLSELECTED('Table'),'Table'[Category]),

                VAR __Value = [Percentage]

                VAR __RD = ROUNDDOWN(__Value,2)

                VAR __Decimal = __Value - __RD

            RETURN

                ROW(

                    "Decimal", __Decimal

                )

        )

    VAR roundDif = SUMX(__Table,[Decimal]) * 100

    VAR maxCategory = CALCULATE(MAX([Category]), TOPN(roundDif, ALL('Table'[Category]), ROUND([Percentage], 2) - ROUNDDOWN([Percentage], 2)), VALUES('Table'[Category]))

    VAR category = MAX('Table'[Category])

    VAR results = IF(category = maxCategory, ROUNDUP([Percentage],2), ROUNDDOWN([Percentage],2))

RETURN

    results

[mwegener]

Hi @Anonymous ,

was your problem solved?

[edhans]

I couldn't get @amitchandak 's method to work. Maybe I am not doing something right @Anonymous 

I don't see how it would work. Internally it has the right amounts, but visually, it is rounding up. Technically, if you have four values of 5.5, they will total 22, but if you are visually rounding to no decimals, they will all show as 6 and the user will add them up as 24 even though your report can correctly show 22 as the total. 

If your report is that easy to see differences, I would either show 1 decimal place, or you'd have to do either some data modeling or more sophisticated DAX to have the last value be 1 minus the sum of the other two values. 

Or @amitchandak could chime back in with a more concrete example of how it would work that I am missing. 

[Anonymous]

@edhans , I also could not get @amitchandak's solution to work.

In essence, this is the dataset (dummy values)

Customer ID	Type of Vehicle	Status
ABC1232	Car	Employed
RAR1313	Bicycle	Unemployed
ADKD1144	Motorcycle	Unknown
AEAA2621	Scooter
ORJW1252		Job Searching
XA4121	Motorcycle	Employed
AEW0001	Scooter	Unemployed
AEW1111	Car	Employed

And translated into a graph:

And this is the Measure created to show the %:

% Measure =
DIVIDE (
COUNT ( 'Table1'[Customer ID] ),
CALCULATE (
COUNTX (
FILTER (
'Table1',
ISBLANK ( 'Table1'[Type of Vehicle] ) = FALSE ()
&& ISBLANK ( 'Table1'[Status] ) = FALSE ()
&& [Status] <> "Unknown"
),
'Table1'[Customer ID]
),
ALL ( 'Table1'[Status] )
)
)

Even after adding the 1.0 into the above DAX, it doesn't seem to work.

[amitchandak]

@Anonymous , please find the file attcahed after signature. I think data was not sufficient to get output like yours. So done some changes, which you do not have to do

Two get data like .33 I added a additional row and used allexcept in place of all

What you have to do is

 *1.0 in formula and decimal data type

[Anonymous]

Hi @amitchandak,

Your solution doesn't provide the correct percentage though:

What I would expect is that Motorcycle would be 100% (as I have filtered out for where "Status" = Unknown.

Bicycle should be 100% as there was only one instance of Bicycle.

Well, Scooter would be 0% as I also filter out for "Status" = Blank

[v-juanli-msft]

Hi @Anonymous 

When changing the number of decimal numbers to 0, the column would roundup to the nearest digit number.

It is by design, we could find some workarounds to adjust the result.

PLease let me know, if there are many percentages which its decimal places are bigger than 0.5,

which do you want to roud up and whcih to round down?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft Can I assume that this a Power BI issue then? I don't have any percentages in the underlying data, I just want to compute percentages within sub-categories using a simple count, i.e. Count of Customer ID/Count of Customer ID (which are part of Sub-Category A and B).

It seems that power bi doesn't recognise when it needs to being totalling up to 100% when calculating for Row Total? (not Grand Total).

[edhans]

@Anonymous this is not a Power BI issue. this is a Math Issue. Look at this in Excel:

These are the exact same columns, one formatted to 2 places, one to zero places.

They both correctly add up to 100%. However, if you took a calculator to column 2, you would get 102%. But internally it is correclty using the 5.5% for the first three items and 83.5% for the last one. If you used ROUND(column,0) and then added, you would get 102%, which is what the human looking at your report is doing because they can only see what you show, and do not see the decimals.

[amitchandak]

@Anonymous , Multiple it by 1.0 and change the data type to decimal

Divide([A]*1.0,[Total A])