Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total using multiple criteria

Good Morning All, 

 

Hoping to get you help on something that has been defeating me as of late. I have a data set of On-Time Delivery for my company and what I am hoping to acheive is being able to create a way to evaluate a running total of received quantity we have gained over time to evaluate our suppliers both on WHEN we received product and HOW much we received. Below is the data set in a screen shot:

 

 

 

In the photo the highlighted columns are those items I am hoping to have run the Measure/Column, with the red underlined columns being what is evaluated. As can be seen the "OrderedQuantity" column will have multiple values that are the same with "ReceivedQuantity" values showing a running receipt. I am hoping to find a way to sum the "Recevied Quantity" values based on the highlighted areas above and then compare the actual difference in "OrderedQuantity" and "Received Quantity." Hopefully this helps. 

 
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous Yeah I'm not understanding. As long as you are making sure your values in the visual are summing and not counting it shouldn't matter if the data is different as the visual function like pivot tables in excel.

     

    aNd if you need the count, just make sure it isn't using dinstinct count.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey Anonymous 

     

    This is where Power BI shines. Assuming I understand your problem correctly I believe what you need is a table (or matrix) accompanied by multiple slicers.

     

    First off, for your difference, you can simply create a calculated column = OrderedQuantity - ReceivedQuantity

     

    Then create a table visual with all of your highlighted and underlined columns (as well as the new calculated column).

     

    Then create slicers for the item number and vendor name

     

    For a date (timeline) slicer I recommend using the one in this link: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview. This is where you will use ItemArrivedJournalDate.

     

    Using these slicers will dynamically filter your table and the calculated column for whatever selection you make. Let me know if this helps.

     

    If this helps please kudo.

    If this solves your problem please accept it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This works to a certain extent for visuals. My problem (which I think may be too complicated) is that a lot of the data for OrderedQuantity and PONumber share the same data, whereas ReceivedQuantity differs. This is where I have been trying to do a running total to have the ReceivedQuantity column sum values associated to a condition so that way I can then take the associated OrderQuantity difference. Not sure if that makes sense. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous Yeah I'm not understanding. As long as you are making sure your values in the visual are summing and not counting it shouldn't matter if the data is different as the visual function like pivot tables in excel.

         

        aNd if you need the count, just make sure it isn't using dinstinct count.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Would you like something below:

    If so, please create measures

    running total_r =
CALCULATE (
    SUM ( 'Table'[ReceivedQuantity] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Vendor Name]
            = MAX ( 'Table'[Vendor Name] )
            && 'Table'[ItemNumber]
                = MAX ( 'Table'[ItemNumber] )
            && 'Table'[Date]
                <= MAX ( 'Table'[Date] )
    )

running total_O =
CALCULATE (
    SUM ( 'Table'[OrderedQuantity] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Vendor Name]
            = MAX ( 'Table'[Vendor Name] )
            && 'Table'[ItemNumber]
                = MAX ( 'Table'[ItemNumber] )
            && 'Table'[Date]
                <= MAX ( 'Table'[Date] )
    )

diff = [running total_r]-[running total_O]

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.