cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STS_Joshua Helper II
Helper II

Filtering Doozy

I'm creating a shipping goal report and I am running into a wall and need some assistance.

 

The idea is that after an order comes in, it has 48 hours to be picked. We are trying to accomplish two things: 1) set a goal for each day, 2) summarize the orders that comprise that goal for our warehouse.

 

Sample Data Set with the current date being 6/5/2019:

Import DateDay of WeekSales Order IDTotal CostShip Date
6/4/201931$40 
6/4/201932$506/4/2019
6/4/201933$35 
6/4/201934$30 
6/4/201935$506/4/2019
6/5/201946$35 
6/5/201947$50 


The goal for the day should be calculated as such: half the value of those orders sent to the warehouse the current day + the second half of the value of orders sent the previous day. The second part of the goal I can figure out by setting it equal to everything from the previous day without a shipdate. The first part becomes tricky though.

The final result should look like:

Shipping Goal for Today ($140) = Sum of value of orders 1,3,4 ($105) + Half of orders 6,7 ($35)

List of orders making up the goal being : 1, 3, 4, 6 

Shipping Goal for Tomorrow ($50) = Other half of 6, 7 ($50)

List of orders making up the goal: 7

 

Because I am trying to also show which orders need to be picked to meet the goal I can't just sum everything up and divide by two (which is why half of orders 6 and 7 is the value of order 6 and not $42.5).

 

I have it currently worked out to show what the total goal for the 48 hour window is using:

$$Goal = CALCULATE(SUM('Query2'[TotalCost]),DAY('Query2'[ImportDate])<=DAY(TODAY()),DAY(Query2[ImportDate])>DAY(TODAY())-3)

I just need help with the splitting of the window into two semi-equal halves.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Filtering Doozy

Hi @STS_Joshua ,

As I said, you can use median function to find out the median order id based on date, then use min order and median order to filter records.
Sample measure formula:

MEDIAN Order Total = 
VAR _min =
    CALCULATE (
        MIN ( Table3[Sales Order ID] ),
        ALLSELECTED ( Table3 ),
        VALUES ( Table3[Import Date] )
    )
VAR _mid =
    CALCULATE (
        MEDIAN ( Table3[Sales Order ID] ),
        ALLSELECTED ( Table3 ),
        VALUES ( Table3[Import Date] )
    )
VAR currID =
    MAX ( Table3[Sales Order ID] )
VAR _list =
    CALCULATETABLE (
        VALUES ( Table3[Sales Order ID] ),
        FILTER (
            ALLSELECTED ( Table3 ),
            IF (
                currID <= _mid,
                [Sales Order ID] >= _min
                    && [Sales Order ID] <= _mid,
                [Sales Order ID] > _mid
            )
        ),
        VALUES ( Table3[Import Date] )
    )
RETURN
    CALCULATE (
        SUM ( Table3[Total Cost] ),
        FILTER ( ALLSELECTED ( Table3 ), [Sales Order ID] IN _list ),
        VALUES ( Table3[Import Date] )
    )

20.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

3 REPLIES 3
Community Support
Community Support

Re: Filtering Doozy

HI @STS_Joshua ,

According to your description, it sounds like you want to calculate today 'total sales' and half 'order id' list total amount?

If this is a case, you can consider to use median function to find out median 'order id' and use it as condition to filter tomorrow id list to get half of list to calculate total sales.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
STS_Joshua Helper II
Helper II

Re: Filtering Doozy

Sort of. When an orders from a customer are imported to our database the warehouse has 48 hours to fill it. Their goal for the day will be half of the orders imported that day with the remaining half being part of their goal for the next day. This is simple enough, take the value for all of the orders from that day and divide it in half. The problem comes in that not every order is the same value and I need a list of all the orders that are part of the goal for the day.

 

In power BI I'd still get a list of all of the orders imported that day because they are all still part of that calculation. I need a way to split the orders for the day in half somewhat evenly first, then add them together. 

Community Support
Community Support

Re: Filtering Doozy

Hi @STS_Joshua ,

As I said, you can use median function to find out the median order id based on date, then use min order and median order to filter records.
Sample measure formula:

MEDIAN Order Total = 
VAR _min =
    CALCULATE (
        MIN ( Table3[Sales Order ID] ),
        ALLSELECTED ( Table3 ),
        VALUES ( Table3[Import Date] )
    )
VAR _mid =
    CALCULATE (
        MEDIAN ( Table3[Sales Order ID] ),
        ALLSELECTED ( Table3 ),
        VALUES ( Table3[Import Date] )
    )
VAR currID =
    MAX ( Table3[Sales Order ID] )
VAR _list =
    CALCULATETABLE (
        VALUES ( Table3[Sales Order ID] ),
        FILTER (
            ALLSELECTED ( Table3 ),
            IF (
                currID <= _mid,
                [Sales Order ID] >= _min
                    && [Sales Order ID] <= _mid,
                [Sales Order ID] > _mid
            )
        ),
        VALUES ( Table3[Import Date] )
    )
RETURN
    CALCULATE (
        SUM ( Table3[Total Cost] ),
        FILTER ( ALLSELECTED ( Table3 ), [Sales Order ID] IN _list ),
        VALUES ( Table3[Import Date] )
    )

20.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors