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
schwinnen
Helper V
Helper V

Formula Help

I am looking to do a formula that shows # of shipments yesterday.  I then will have a table where I have a list of carriers and I can see how many shipments each had yesterday.  I created the following measure:

 

Shipments Yesterday = 
        CALCULATE(
            SUM('public vw_shipment_quality_assurance'[LOADS]),
            FILTER(ALLSELECTED('public vw_shipment_quality_assurance'), 'public vw_shipment_quality_assurance'[ROUTE START DATE]
                = TODAY()-1))
                

This measure does calculate the total shipments for yesterday successfully, however when I add it to the table with the carriers, it just lists the total number of shipments next to each carrier.  In other words, every carrier has 3593 next to it, instead of that carrier's total.

Any ideas on how this should work?

1 ACCEPTED SOLUTION

@schwinnen -

 

Well, knowing that, maybe this will work:

 

Measure 2 =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Carrier], Table1[Customer] ),
        Table1[Date]
            = TODAY () - 1
    )
)

Should yield:

 

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
ChrisMendoza
Resident Rockstar
Resident Rockstar

@schwinnen -

 

Try the following [Measure] :

 

Measure =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Carrier] ), Table1[Date] = TODAY () - 1 )
)

The image shows [Pieces] by date in the top matrix and a matrix with [Carrier], your [Shipments Yesterday] and [Measure]

 

1.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thank you, @ChrisMendoza.  This works, but I have one other problem.  I have a report level filter on that filters out a specific customer and this formula does not seem to be recognizing that filter.  I think maybe this was why I was using ALLSELECTED.  Any ideas on how to get around that?

@schwinnen -

 

Well, knowing that, maybe this will work:

 

Measure 2 =
CALCULATE (
    SUM ( Table1[Pieces] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Carrier], Table1[Customer] ),
        Table1[Date]
            = TODAY () - 1
    )
)

Should yield:

 

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Thank you so much, @ChrisMendoza.  That seems to work perfectly.  I assume this means that if I add any other visual, page or report level filters I will have to add them to the ALLEXCEPT formula?

@schwinnen -

 

I can't say, as I am still learning as well. Here is a link that may help you further your knowledge https://dax.guide/allexcept/. You'll notice they link to articles that further explain as well.

 

Maybe someone else in the community has more detail they can expand on?

 

A suggestion for you though would be provide details that could impact the solution (i.e. the Report Filter) or better yet the dataset(s) you're actually working with.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.