Dynamic calculations for calculated columns based on a filter

Nerdio 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,645 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,526 Reputation points
    2021-04-20T07:29:46.377+00:00

    @Nerdio

    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'))).
    89432-capture29.png

    Then I creare a Power Pivot table, as following image, you may filter the "Despatched Date", then you would get the percentage of each item code.
    89389-capture30.png

    Please note, to get the format of percentage, we need to choose the column of "Result2" in Power Pivot table > Click Value Field Settings > Show Values As tab > Number Format > Percentage.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments