- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 09:42 AM

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?

Solved! Go to Solution.

Accepted Solutions

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 11:23 AM

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:

All Replies

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 10:01 AM

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]

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 10:33 AM

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?

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 11:23 AM

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:

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 11:31 AM

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?

## Re: Formula Help

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 11:45 AM

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.