Distribute the total number of lines equally

[Spotto]

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 🙂

[v-xinruzhu-msft · Accepted solution]

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.

[v-xinruzhu-msft · Accepted solution]

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.

[Spotto]

@v-xinruzhu-msft  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 

[v-xinruzhu-msft]

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.

[lbendlin]

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

[Spotto]

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?

[lbendlin]

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).

[Spotto]

@lbendlin 

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

[lbendlin]

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

[Spotto]

@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.

[lbendlin]

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.

[Spotto]

@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?

[lbendlin]

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.

[Spotto]

@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

[Spotto]

@lbendlin tks so much, it worked perfectly 😀