Sum Where issue - Data is between two tables

[publication1]

Im trying to get a SUM of number of items within a table where it matches another table. Ill keep it simple

Table one  has columns (Item_Count, Item_floor)

Table two has (Item_floor)

Im trying to sum Item_count) where Item_floor is a match in both tables

I have tried this method

Sum({<[TABLE_ONE.ITEM_FLOOR]=[TABLE_TWO.ITEM_FLOOR]>}[ITEM_COUNT]) - This gives me an output of 0

I have also tried this method

=if([TABLE_ONE.ITEM_FLOOR]=[TABLE_TWO.ITEM_FLOOR],Sum(ITEM_COUNT),'N') - This is giving me an output of N

Thanks in advance for your time 🙂

[agigliotti · Accepted solution]

maybe this:

=Sum( if( [TABLE_ONE.ITEM_FLOOR] = [TABLE_TWO.ITEM_FLOOR], ITEM_COUNT, 0 ) )

I hope it helps.

[MayilVahanan]

Hi 

In TableB, you can bring one dummy field like below

TableB:

Load ITEM_FLOOR, 1 as dummy from datasource;

And use in set analysis like

Sum({<dummy={1}>}[ITEM_COUNT])

[publication1]

exactly what I was after, thank you