Running Total using multiple criteria

[Anonymous]

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. 

[v-juanli-msft]

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.

[Tad17]

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]

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. 

[Tad17 · Accepted solution]

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

[Anonymous]

@Tad17  You were right actually what I came to find out was that the division I was doing using DAX was not done correctly and the summarizing was all wrong. Thank you!