Measure to put data into categories

[Anonymous]

Hello Power BI-Community,

I am dealing wit the following Problem.

I got a dataset, similar to the columns in the picture below, lets say the example number shows us the amount of public parks to repair for a company:

Now i have to put the data into categories for a customer,, like a roadmap where he should start to repair parks.

the categories each include 2000 parks and start with the highest value.

 So i can put them into categories by looking at the running total.

Now i got the following problem. each category should only contain countries of the same continent.

In this example category 1 would start by germany till russia, then skip all countries which are not europe and continue with austria and other european countries till 2000. category 2 should then start with USA because its the highest example number, not used till now in a category, an then continue with the same logic and so on.

Is there any way to master this case?

Thanks for your help

[v-zhangti]

Hi, @Anonymous 

There is a relatively simple method I do not know if it can solve your problem.

categories = VAR N1=SWITCH(TRUE(),
[Continent]="Europe",1,
[Continent]="North American",2,
[Continent]="South American",3,
[Continent]="Asia",4)
return N1

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hello @v-zhangti ,

that doesnt fit for my Solution.

I go a little bit more in detail.

As you can see I got a list with an example number wich should be counted in an extra column like a running total. It should start counting with the highest value and then go on. That i can do with RankX.

After that I need a column "category" to say my customer where to start. Therefore I need categories which include countries with a sum of "2000". So you start on the top of the example number, till the running total is 2000, then continue withe the next category(2000-4000) and so on. Because ist unlogicaly that you repair 4 parks for example in europe and one in asia in one step, every category should only have countries of the same continent. 

in This example category 1 would start with germany because it has the highest example number, so the most parcs to repair, and then continue till the runnung total is 2000, because USA till japan is outside europe it continues with Austria. Then its a sum of 2000, and now category 2 starts with the USA because it has the highest exmaple number actually without a  category and continues the logig described.

[Whitewater100]

Countries and Parks 

I hope I understood your question. Please see attached potential solution.

Thanks!

[Anonymous]

Yes, thats what i want. a column where you can see the categories to tell my customer the following.

In the first step you have to repair ...(all category 1)

In the second step you have to repait ... (all category 2)

[ValtteriN]

Would something like this suffice:

RT by continent = var ind = MAX('RT categories'[Index]) return

CALCULATE(SUM('RT categories'[Value]),ALLEXCEPT('RT categories','RT categories'[Continent]),'RT categories'[Index]<=ind)

Category_ =

var _floor =FLOOR([RT by continent]/2000,1)+1

return

"Category " &_floor & " " &MAX('RT categories'[Continent])

End result:

[Anonymous]

Thank you very much!

I have one point to add. The running total should start with the highest Value and then go desc.

[ValtteriN]

Hi,

I am a bit unclear what is your desired end result. Could you described what kind of values would you like to return? E.g. Do you want a column with values like "Category 1"