Complicated Measure filtering multiple columns from different tables

[Nicci]

Hi All, I'm in need of some severe help. I've been stuck on this issue for a few weeks now 😞

I have a bunch of tables with financial data but here are the main ones:

Table 1 - List of expenses by purchase order number and what FY it expires.

Table 2 - Total cost for all purchase order numbers, FY it was purchased in, and a flag if it's a blanket order or not.

The 2 tables are relationshiped on the purchase order number.

Scope:

1. Have the total purchased for the FY chosen from the slicer from Table 2.

2. Have a total of purchase orders from Table 1 that are expiring next fiscal year which will be the FY after the FY chosen in the slicer and is NOT a blanket order (flag and totals live in table 2)

3. Have the total of #1 and #2 combined for final total needed.

I am able to do #1 in the scope. I have a card visual that sums the total from Table 2. The slicer takes care of the filter.

I also was able to create a card for #2 but not without some heavy filtering on the card itself. My issue here is because I'm filtering the visual I do not know how to take the outcome of the filters so I can make a 3rd card that totals card 1 and 2 together. I cant add the same filtering on the 3rd card because some of those filters will remove totals from scope #1.

Help! LOL 

Thank you!

[technolog · Accepted solution]

For the first scope, you've already managed to get the total purchased for the FY chosen from the slicer from Table 2. That's great! Let's call this measure TotalPurchasedFY.

For the second scope, you want to get the total of purchase orders from Table 1 that are expiring next fiscal year, which is the FY after the one chosen in the slicer, and is NOT a blanket order. Let's create a DAX measure for this:

TotalExpiringNextFY =
VAR CurrentFY = SELECTEDVALUE('Table 2'[FY])
VAR NextFY = CurrentFY + 1
RETURN
SUMX(
FILTER(
'Table 1',
'Table 1'[ExpiryFY] = NextFY
),
CALCULATE(
SUM('Table 2'[TotalCost]),
'Table 2'[BlanketOrderFlag] = 0
)
)
In this measure, we first determine the current FY from the slicer and then calculate the next FY. We then filter Table 1 for rows where the expiry FY matches the next FY. For each of these rows, we sum the total cost from Table 2 where it's not a blanket order.

Now, for the third scope, you want to combine the totals of the first two scopes. This is straightforward:

FinalTotal = TotalPurchasedFY + TotalExpiringNextFY
You can now use the FinalTotal measure in a card visual to get the combined total of the first two scopes.