Multiple filter help

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
I need to apply two different filters to a measure. I can do them individually but cannot figure out how to combine them. I want to get total pounds of linen per month.

For a table "Forecast"
[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]Member
[/TD]
[TD]Linen Type
[/TD]
[TD]Activity
[/TD]
[TD]Weight
[/TD]
[TD]Linen Group
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Wash
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Dry
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019
[/TD]
[TD]Member A
[/TD]
[TD]Scrubs
[/TD]
[TD]Iron
[/TD]
[TD]500
[/TD]
[TD]MemberA_Scrubs
[/TD]
[/TR]
[TR]
[TD]Feb 22, 2019[/TD]
[TD]Member A[/TD]
[TD]Gowns[/TD]
[TD]Wash[/TD]
[TD]700[/TD]
[TD]MemberA_Gowns[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have a monthly forecast of laundry usage by cooperative member, but the core information is at a different level of aggregation. Each type of linen (e.g. scrubs) goes through multiple actions such as washing, drying, and folding. The data provides the total pounds of scrubs, so if we have 500 lbs of scrubs that's 500 lbs for each of the activities but still only 500 lbs total for the member.

The data only has rows for the start date. Member A will continue to process 500 lbs of scrubs a month.

I have a measure that will project the usage into the future.

Code:
Monthly Forecast Lbs:=SUMX (
    CALCULATETABLE (
        Forecast,
        FILTER ( Forecast, Forecast[Start Date] <= MAX ( 'Calendar'[Date] ) )
    ),
        Forecast[Weight] 
    )

But it's adding the all the weights together for the months (e.g. giving me 1,500 lbs instead of 500).

I have a measure that will properly group the weight

Code:
Forecast Lbs:=SUMX (
    VALUES (Forecast[Linen Group]),
    CALCULATE ( MAX (Forecast[Weight] )
)
)

But it's not projecting into the future. How can I combine these measures - or have a new one - that will both sum the weight as well as project into the future? Thanks.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Have you tried:

Code:
Forecast:=
Calculate (
    [Forecast Lbs],
    FILTER ( 
        Forecast, 
        Forecast[Start Date] <= MAX ( 'Calendar'[Date] ) 
    )
)
 
Upvote 0
Oh my, how embarrassing I didn't seem to try that seemingly obvious permutation! Thanks very much.
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top