# Dynamic calculations for calculated columns based on a filter

1 Reputation point
2021-04-15T07:41:32.91+00:00

I have a table in Power Pivot that looks something like this

Despatched Date, Order ID, Item Code, Quantity
1-Apr-21, Order-1, Item-A, 5
1-Apr-21, Order-2, Item-B, 3
2-Apr-21, Order-3, Item-A, 2
2-Apr-21, Order-3, Item-B, 2
2-Apr-21, Order-3, Item-C, 1

What I want to be able to calculate, ultimately, is what proportion of the despatched orders each item makes up. This then maps to ranges like fast, medium and slow indicating if the item was fast moving etc. So, for example;

If I filtered for 1st April 2021 only there were 8 items shipped across two items. Item-A was 5, Item-B was 3.

If I filtered across 1st April 2021 to 2nd April 2021 there were 13 items shipped. Item-A was 7, Item-B was 5 and Item-C was 1

I think what I really need here is a separate table of Item Codes and the calculated numbers for each Item Code. I just don't know how to tackle this. Can someone give me some guidance please. (Sorry I don't have an example Data Model at this time as I am just 'playing' with the concept).

Thanks

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,697 questions

According to your sample, I create a measure in Power Pivot: "Result2", the formula is `=DIVIDE(SUM('Table1'[Quantity]),CALCULATE(SUM('Table1'[Quantity]),ALLSELECTED('Table1')))`.