Adjusting values based on rankings

[LBala]

I am having trouble building a measure to accomplish what's described below.
I am working with the following scenario:
- There are 2 types of balls: green and red.
- There are 3 types of buckets: A, B and C.

The balls are randomly dropped inside the buckets. The table below shows how many balls are inside each bucket:

Ball_color	Bucket-A	Bucket-B	Bucket-C	Total
Green	1	2	1	4
Red	2	3	1	6

<tbody>
</tbody>

Now, the table below shows how many balls, by color, should be removed from the buckets:

Ball_color	Remove
Green	2
Red	3

<tbody>
</tbody>

The balls should be removed from the buckets starting from the lowest ranked bucket for each ball color.
Below is the rank of each bucket by ball color:

Ball_color	Bucket-A	Bucket-B	Bucket-C
Green	3	2	1
Red	1	2	3

<tbody>
</tbody>

The table below shows how the final result looks like after the balls are removed starting from the lowest ranked buckets:

Ball_color	Bucket-A	Bucket-B	Bucket-C	Total
Green	0	1	1	2
Red	2	1	0	3

<tbody>
</tbody>

Explaining the case of the green ball: the Bucket-A is ranked lowest (rank = 3) among the 3 buckets and the quantity in the Bucket-A dropped from 1 to 0. Then the Bucket-B (rank = 2) had its quantity lowered from 2 to 1. Result: total of 2 green balls removed.

Question: Is it possible to build a measure to give me the results showing in the table above? Basically I need a measure to calculate the quantity of balls to be removed from each bucket according to their rankings by ball color.

------------------------------------

These are the tables I am working with:

Table: Remove

Ball_color	Remove
Green	2
Red	3

<tbody>
</tbody>

Table: Quantity

Ball_color	Bucket_ID	Line_ID	Quantity
Green	Bucket-A	Green-A	1
Green	Bucket-B	Green-B	2
Green	Bucket-C	Green-C	1
Red	Bucket-A	Red-A	2
Red	Bucket-B	Red-B	3
Red	Bucket-C	Red-C	1

<tbody>
</tbody>

Table: Rank

Ball_color	Bucket_ID	Line_ID	Rank
Green	Bucket-A	Green-A	3
Green	Bucket-B	Green-B	2
Green	Bucket-C	Green-C	1
Red	Bucket-A	Red-A	1
Red	Bucket-B	Red-B	2
Red	Bucket-C	Red-C	3

<tbody>
</tbody>

The tables "Remove" and "Quantity" are linked by the column "Ball_color".
The tables "Quantity" and "Rank" are linked by the column "Line_ID".

Any help is appreciated. Thanks.

 

[MarcelBeug]

It looks more like a new table than a measure, so my suggestion would be to use Power Query.

I loaded your tables into Power Query and from there:

I combined tables Quantity and Rank in Query/table QuantityRank:

let
    Source = Table.NestedJoin(Quantity,{"Line_ID"},Rank,{"Line_ID"},"Rank",JoinKind.LeftOuter),
    ExpandedRank = Table.ExpandTableColumn(Source, "Rank", {"Rank"})
in
    ExpandedRank

I created query FinalResult:

let
    Source = Table.NestedJoin(Remove,{"Ball_color"},QuantityRank,{"Ball_color"},"QR",JoinKind.LeftOuter),
    Sorted = Table.TransformColumns(Source,{{"QR", each Table.Sort(_,{{"Rank", Order.Descending}}), type table}}),
    AddedStillToRemove = Table.AddColumn(Sorted, "StillToRemove", each List.Accumulate([QR][Quantity],{[Remove]},(StillToRemove,Quantity) => StillToRemove & {List.Max({0,List.Last(StillToRemove)-Quantity})}), type {Int64.Type}),
    AddedNewTable = Table.AddColumn(AddedStillToRemove, "NewTable", each Table.FromColumns(Table.ToColumns([QR])&{List.FirstN([StillToRemove],List.Count([StillToRemove])-1)}), type table),
    RemovedColumns1 = Table.RemoveColumns(AddedNewTable,{"Remove", "QR", "StillToRemove"}),
    ExpandedNewTable = Table.ExpandTableColumn(RemovedColumns1, "NewTable", {"Column2", "Column4", "Column5", "Column6"}, {"Bucket_ID", "Quantity", "Rank", "StillToRemove"}),
    AddedNewQuantity = Table.AddColumn(ExpandedNewTable, "NewQuantity", each List.Max({0,[Quantity]-[StillToRemove]}), Int64.Type),
    RemovedColumns2 = Table.RemoveColumns(AddedNewQuantity,{"Quantity", "Rank", "StillToRemove"}),
    PivotedBucket = Table.Pivot(RemovedColumns2, List.Distinct(RemovedColumns2[Bucket_ID]), "Bucket_ID", "NewQuantity"),
    InsertedSum = Table.AddColumn(PivotedBucket, "Total", each List.Sum(List.Skip(Record.FieldValues(_))), Int64.Type)
in
    InsertedSum

 

[LBala]

Hi Marcel, your solution works perfectly, it indeed accomplishes the end result I need, thank you very much.
Quick question: In your opinion, is it possible to have a measure doing this adjustment instead of having to create a new table?

 

[MarcelBeug]

As far as I know, a measure is basically 1 value, that may vary according to the filter context.
Maybe you mean a calculated column instead of a measure?

Anyhow, my opinion about measures and calculated columns isn't worth too much, as I'm really not a DAX expert.

 

[LBala]

I meant a measure but the new table solution you provided is a nice way to get the solution to the problem.
Thanks.

 

[Ozeroth]

LBala,

This could be handled with a measure. I would also recommend some small changes to the data model.
Which version of Excel are you using, or are you using Power BI Desktop? Just want to know whether you are using a version DAX with variables before making any suggestions.

Regards,
Owen

 

[LBala]

Ozeroth;


I am using Excel 2016 64-bit. How would you set up the tables in order to have a measure doing the calculation?


Thanks in advance.