Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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. 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.