Group by sum of running percentage total

[NiugeS]

Hi all,

Still trying to learn BI and looking for some guidance.  I'll try and explain as best I can what i'm trying to achieve.

I have a table of Teams.  I have another table that provides the percentage of how much that team played over a period.  Using this percentage, I need to rank the teams from highest to lowest percentage, and work out which group they fall into. 

In the example below Team G played 8% and Team D played 5%.  Both fit into the group Top 15 as their total is less then 15.  Team C who played 4% and Team B who played 2% fall into the Top 20 as the running total falls under 20% and so on.

Teams	Percent	Running Total	Group
Team G	8%	8%	Top 15
Team D	5%	13%	Top 15
Team C	4%	17%	Top 20
Team B	2%	19%	Top 20
Team E	2%	21%	Top 25
Team F	1%	22%	Top 25
Team A	0.50%	23%	Top 25

No idea where to start with this so any help appreciated.

Many thanks

[Anonymous]

@NiugeS 

You use Switch() to get the total group.

total group = Switch(True(),
[Percent running] <= 15%, "TOP 15",
[Percent running] >15% &&[Percent running] <=20%, "TOP 20",
[Percent running] >20% &&[Percent running] <=25%, "TOP 25"))

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

[NiugeS]

@Anonymous I am still trying to resolve this.  I created a calculated column and tried the below but it does not like the % percentage symbol and gives me error.  Am I doing something wrong?

Thank you

[Ashish_Mathur]

Hi,

Have you already been able to calculate the Running total column?  If yes, then what is the use of the Group column?

[NiugeS]

@Ashish_Mathur Hi - I only have the first two columns - teams and percent.

[Ashish_Mathur]

Hi,

Is the Percent column a calculated column/measure or is that raw data?  Share the link from where i can download your PBI file.

[NiugeS]

Hi @Ashish_Mathur the percentage is raw data.  As requested Test PBIX 

I randomly put these numbers together but the percentage is calculated elsewhere.

I have a long list of teams and a long list of sales for example and the percentage reflects the percentage of total sales.  I then want to calculate if that team is in the top 15% of all sales.

[Ashish_Mathur · Accepted solution]

Hello

You can download my PBI file from here.

I hope this helps.

[NiugeS]

@Ashish_Mathur Many thanks for all your help. This is great and i'm trying to put into use.

I've unfortunately realised that the percentage needs to be calculated by a measure as the raw data is % of Grand Total and if I filter in anyway, it doesn't filter the percentage.

I've created a measure called % Total but it will not allow me to replace PERCENTAGE in the Rank and Running Total. Without spending much more time on this as you've been great already, is there an easy fix?

Many thanks

[Ashish_Mathur]

Hi,

This is exactly what i had asked you earlier.  You said that Percentage can be treated as an input.  Now please think over your question very carefully and tell me exactly what your raw data  is how you want to calculate the Percentge column.

[TomMartens]

Hey @NiugeS ,

you can use the static segmentation pattern, described here: https://www.daxpatterns.com/static-segmentation/

Hopefully, this provides what you are looking for.

Regards,

Tom