topic Re: Distribute the total number of lines equally in Power Query

Distribute the total number of lines equally

Spotto — Mon, 11 Mar 2024 21:07:40 GMT

Hi everyone, I have a simple example for understanding.

I need to distribute the total records from table A to table B EQUALLY

*** Unfortunately, I don't have a field for relationships between tables

TABLE A

CONTRACT - VALUE

A 10

B 20

C 5

D 50

E 15

F 20

G 15

TABLE B

NAME

MARCELO

ROBERTA

EXPECTED RESULT:

TABLE C

NAME CONTRACT VALOR

MARCELO A 10

MARCELO B 20

MARCELO C 5

ROBERTA D 50

ROBERTA E 15

ROBERTA F 20

ROBERTA G 15

If the division between contracts by analysts does not result in a complete result, an analyst may receive more contracts

Any suggestions on how to do this distribution?

tks for help 🙂

Re: Distribute the total number of lines equally

lbendlin — Mon, 11 Mar 2024 22:13:32 GMT

Does the size of the contract play any role? Could you use PERCENTILEX.INC(...,0.5) ?

Re: Distribute the total number of lines equally

Anonymous — Tue, 12 Mar 2024 05:19:00 GMT

Hi Spotto

You can create two blank queries and put the following code to advanced editor in power query

Query1:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjKNIExnINMUzHIBsSCCriClEFE3hFJ3qGgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CONTRACT " = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONTRACT ", type text}, {"Value", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type) in #"Added Index"

Query2:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nUMcnb18VeK1YlWCvJ3cg0KcQSzk5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=List.Max(#"Query1"[Index]), b=List.Max(#"Added Index"[Index]), c=Number.RoundDown(a/b), d=([Index]-1)*c in if [Index]=b then List.Numbers(d+1,a-d) else List.Numbers([Index]*c-c+1,c)), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Custom"}, #"Query1", {"Index"}, "Query1", JoinKind.LeftOuter), #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"CONTRACT ", "Value"}, {"CONTRACT ", "Value"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Index", "Custom"}) in #"Removed Columns"

Output

Best Regards!

Yolo Zhu

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

Re: Distribute the total number of lines equally

Spotto — Tue, 12 Mar 2024 12:07:46 GMT

Anonymous tks for help me , but I tested the sample.pbix file and I didn't understand why it has the "C" analyst?

I couldn't reproduce the result as shown in your answer

Re: Distribute the total number of lines equally

Spotto — Tue, 12 Mar 2024 12:10:05 GMT

The objective is to distribute the number of rows in table A to the analysts in table B. Could you give me an example using PERCENTILEX.INC?

Re: Distribute the total number of lines equally

lbendlin — Tue, 12 Mar 2024 12:59:06 GMT

Here is how it would look like for your sample data.

The first person would get the first three contracts and the second person all the other ones (the long tail).

As your sample size is small this will skew heavily in the favor of the first person. With larger sample sizes that issue will be mitigated.

An alternative would be a process to

- pick the highest value for person 1

- keep picking the next highest values for person 2 until the sum is higher than what 1 has

- repeat the process until everything is distributed.

That would result in the following for your sample :

D 1

B 2

F 2

E 2

G 1

A 2

C 2

with a total of 65 for person 1 and 70 for person 2.

Obviously this is not something you should even attempt to do in Power BI.

Unless - you have some more realistic sample data. I'd be willing to play with fire and try this in Power Query. (You would always have the alternative to use Python or R code, but that comes with severe limitations).

Re: Distribute the total number of lines equally

Spotto — Tue, 12 Mar 2024 14:29:11 GMT

lbendlin

Here is a larger sample, tks for help me
Example.xls

Re: Distribute the total number of lines equally

lbendlin — Tue, 12 Mar 2024 14:58:50 GMT

You have multiple rows for each contract. Should they all be assigned to the same rep or can they be distributed?

Re: Distribute the total number of lines equally

Spotto — Tue, 12 Mar 2024 15:12:49 GMT

lbendlin Even if you have repeated contracts, you can distribute them to other analysts, yes, because the complete database will have other columns that I will make a key for.

Re: Distribute the total number of lines equally

lbendlin — Tue, 12 Mar 2024 17:55:14 GMT

You have negative contract values !!! Not cool!

Here is a Power Query based solution. Power BI was unable to complete this, it ran out of memory.

There's a bug with the last contract, but maybe you can figure that out yourself.

Re: Distribute the total number of lines equally

Anonymous — Wed, 13 Mar 2024 00:53:02 GMT

Hi Spotto

The c just for test, you can remove it , I have remove the c, you can see the new attachments.

Best Regards!

Yolo Zhu

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

Re: Distribute the total number of lines equally

Spotto — Wed, 13 Mar 2024 12:16:18 GMT

lbendlin
Thank you for your help and solution but the idea would be to distribute the number of lines or contracts from table A to the analysts and not sum, I did a count by analyst in your file and saw that the lines by analysts are very different would it be possible to please have an adjustment in your presented solution?

Re: Distribute the total number of lines equally

lbendlin — Wed, 13 Mar 2024 14:35:25 GMT

Where's the fun in that? You can use the same approach (you can modify my Power Query code) but it will result in very lopsided value distribution.

Re: Distribute the total number of lines equally

Spotto — Wed, 13 Mar 2024 15:32:30 GMT

lbendlin
This power bi will be used to distribute contracts equally for analysts to carry out analyzes and validations, this will be the fun 🙂
tks for help

Re: Distribute the total number of lines equally

lbendlin — Wed, 13 Mar 2024 16:01:38 GMT

Re: Distribute the total number of lines equally

Spotto — Wed, 13 Mar 2024 17:09:15 GMT

lbendlin tks so much, it worked perfectly 😀