Percent of row in a matrix with zero decimal places ?

[StephenF]

Thres a few of thes posts but no clear answer.

I want a "percentage of row" with no decimal places. the in built "percent of row" insists on two decimal places.

It should be something like this but I need an example that works on the row level only.

% Row = calculate(count(SP[SpotNo]))/calculate(count(SP[SpotNo]),ALLSELECTED(SP))

Any ideas?

Edit:

Please upvote this idea so we have out functionality for this:

https://ideas.powerbi.com/ideas/idea/?ideaid=416fc2e1-e53c-428d-8e2c-8f6f79e13fc8

[v-yanjiang-msft]

Hi @StephenF ,

If you don't want to change the data structure, you can try this solution.

Create five measures, for Kids, the formula is:

Kids% = DIVIDE(MAX('Table'[Kids]),MAX('Table'[Total]))

 Modify the corresponding column in the formula to get other measures.

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @StephenF ,

You should just be able to select your visual, then go to Field formatting in the Format menu, then change the DP you want to display:

Pete

[StephenF]

You should be, Pete but you actually can't. adjusting the decimal places has no effect on the inbuilt menu item "percent of row total".

Its a known issue for many years with this product, you need to use a dax measure instead. I need just the correct code.

[BA_Pete]

Ah, interesting. Fair enough.

Are you able to share a small, anonymised, sample of your data and the expected output please?

Pete

[StephenF]

Here you go

[BA_Pete]

Thanks @StephenF ,

Is this the actual format that the source data is in, or has your example already been partitioned/aggregated into gender and age buckets?

Pete

[StephenF]

The data underlying this has thousands of rows, its aggregated based on a numeric measure and two dimensions in the underlying table.

[BA_Pete]

Cool. That makes sense.

As such, I've restructured your example data like so:

Obviously this is aggregated, but it shouldn't affect the principle.

I've used this measure:

_row% = 
VAR __countVal =
SUM(demoTable[count])
VAR __demoVal =
CALCULATE(
    SUM(demoTable[count]),
    ALLEXCEPT(demoTable, demoTable[demo])
)
RETURN
DIVIDE(__countVal, __demoVal, 0)

Which gives me the following output once formatting has been applied to the measure itself:

Pete

[StephenF]

This works on the face of it.

At the same time all the dropdowns I use to filter the data dont function as would be expected, I have month, year and a slew of others in the raw data. I expect this is due to how AllExcept behaves?

[BA_Pete]

Yes, it's due to the ALLEXCEPT argument being used to provide a very specific outcome. Unfortunately, I've only been able to account for the variables I'm aware of based on your initial post and your Data/Current/Desired examples.

If you're able to provide a more complete set of example data I'm happy to look at a more complete solution for you, otherwise you should be able to update the basic measure structure I've given you so that the __demoVal variable correctly calculates the set of data you need for your denominator. You can try adding your slicer fields into the ALLEXCEPT function but, without seeing your source data in detail, I can't guarantee that this will work as required for your scenario.

Pete