Dynamic grouping of percentage of total

[bilingual]

Name	Numbers	Percentage of total	New Column
A	150	37,5
B	100	25
C	100	25
D	50	12,5

Hi i have made this formula, i want to create a dynamic value, so i can group the percentage according to my needs

For example here, i want to group all values with Less than/equal to 25 percent and name the group "Less than/equal to 25 percent". I want to make it dynamic, so i can auto group according to if i for example change to Less than/equal to 15 percent, so i need a dropdown menu as well

Can somebody help me?

Kind regards Daniel

[v-rzhou-msft]

Hi @bilingual ,

Here I suggest you to try "What if Parameter" to create a percent slicer.

Then create a table visual to show name list whose percentage is less than or equal to select in parameter.

Dynamic Title measure is as below.

Title =
VAR _Parameter =
    SELECTEDVALUE ( Parameter[Parameter] )
VAR _Title = "Less than/equal to" & " " & _Parameter & " " & "percent"
RETURN
    _Title

Measure:

Filter = 
VAR _Parameter =
    SELECTEDVALUE ( Parameter[Parameter] )
RETURN
    IF ( SUM ( 'Table'[Percentage of total] ) <= DIVIDE ( _Parameter, 100 ), 1, 0 )

Add this measure into visual level filter and set it to show items when value equal to 1.

Result is as below.

Best Regards,
Rico Zhou

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

[bilingual]

Hi Rico, the challenge is that the percentage of Total is not a column but a measure = 

"pct. af total = DIVIDE(SUM(Data[Antal rejser]);CALCULATE(SUM(Data[Numbers]);ALL(Data[Name]));BLANK())"

I want to be able to dynamic group the percentage of total according to periode, so it will adjust whether it is for example January or February,

[Greg_Deckler]

@bilingual Create a new table using Enter Data query and populate it with the names of your groups. Make sure it is disconnected or unrelated to any other tables in your data model. You can then use that for your dropdown menu and construct the appropriate measure. More specifics would help in being less general with a solution. Are you simply trying to filter that table depending on what group you select?

[bilingual]

Hi Greg, unfortately it is the other way round, i want to rank the Names according to their size of the percentage of the total.