If then measure returning incorrect totals

[spencer_II]

Sooo.... I have exhausted options, video's and forumns for a solution.  Mutliple attempts with SUMX and SUMX w/ SUMMARIZE. None leading to a correct total.

Customer and Item No-Description are from two different dimension tables. The solutions attempted would have worked if first two columns both existed in same table.

Cnt Fcst equals Bdgt is an if/then measure as follows:

Cnt Fcst equals Bdgt =

VAR doesmatch=IF([Forecast Cases]=[Budget Cases],1,blank())

RETURN

doesmatch

So total is 4, which is correct at an Item No-Description level, But INCORRECT when adding column for Customer ID.  Correct total should be 430.  Each customer/item combination where Budget ='s Forecaset should be 1 and the total (customer/item) would total 430.

Hope this is sufficient to get some feedback on the correct pattern.

As Always, appreciate the help.

Spencer

[spencer_II · Accepted solution]

So here is the fix...  Two seperate tables requires the SUMMARIZE to be used w/ BOTH Filter context and SUMX.

Like the following:

SUMX(

FILTER(

SUMMARIZE('Tbl_Budget2020','Tbl_Customers DIM'[CUSTOMER],'Tbl_Product(ADJ) DIM'[Item No-Description]),

[Cnt Fcst equals Bdgt]=1),

[Cnt Fcst equals Bdgt])

This worked and returned the correct totals with columns from different dimension tables.