cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## calculate sum with criteria from many columns of another filtered table

Hello i want to sum a column but i need to filter the table based on data from another table.

So i have table1 where i want to sum points and i want to sum only the record that for the dates and the names and the classes i find in table 2

 names dates class points george 2020-01-01 math 10 george 2020-02-01 physic 13 george 2020-05-01 physic 16 george 2020-06-01 chem 19 george 2020-07-01 math 22 mairi 2020-01-01 math 25 mairi 2020-02-01 physic 28 mairi 2020-05-01 physic 31 mairi 2020-06-01 chem 34 mairi 2020-07-01 math 37 nick 2020-01-01 math 40 nick 2020-02-01 physic 43 nick 2020-05-01 physic 46 nick 2020-06-01 chem 49 nick 2020-07-01 math 52 helen 2020-01-01 math 55 helen 2020-02-01 physic 58 helen 2020-05-01 physic 61 helen 2020-06-01 chem 64 helen 2020-07-01 math 67 sundy 2020-01-01 math 70 sundy 2020-02-01 physic 73 sundy 2020-05-01 physic 76 sundy 2020-06-01 chem 79 sundy 2020-07-01 math 82

And table2

 name date classes george 2020-01-01 math mairi 2020-02-01 physic nick 2020-05-01 physic helen 2020-06-01 chem sundy 2020-07-01 math

I am using measure like this:

Measure 3 = CALCULATE(sum(Table1[points]);Table1[name] in (ALLSELECTED(Table2[name]));Table1[date] in (ALLSELECTED(Table2[date]));Table1[class] in (ALLSELECTED(Table2[class]))) but it does not filter properly,

is there any better way to do this?
3 REPLIES 3
Resolver I

That depends on how and where you want to display that result.  A simple way is have a relationship between the 2 tables based on Name.  You can then just use a Sum measure, and let the relationships and table do the filtering.

then in a table visual put:

Helper IV

My problem is that connection is many to many and even when i try to join all 3 columns i get the same classic problem where it does not find solution and places everywhere the same number.

Anonymous
Not applicable

instead of allselelcted try ALLEXCEPT. As it will keep filters on those columns which you mentioned in ALLEXCEPT.

Thanks & regards,
Pravin Wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.