2 conditions for Sum between 2 tables

[stanislav_dugas]

Hi PBI experts.

I am having an issue with writing a dax formula for this.

I have 2 tables which have both way relationship many to one.

1st table

ID	Period	Type	Value
1	2020	a	1
1	2020	b	2
1	2020	c	3
2	2020	a	4
2	2020	b	5
2	2020	c	6
3	2020	a	7
3	2020	b	8
3	2020	c	9
1	2021	a	10
1	2021	b	11
1	2021	c	12
2	2021	a	13
2	2021	b	14
2	2021	c	15
3	2021	a	16
3	2021	b	17
3	2021	c	18

2nd table 

ID	Sum Year 2020 type a	Sum Year 2021 type a
1
2
3

What i need to do is create sum of Values for each ID in table 2 where year = "2020" and type = "a" based table 1.

The data above are just example since the real date are more complex and the type has several subtypes which would be combined together on that level.

Thank you all for ideas.

[Fowmy]

@stanislav_dugas 

You can achieve this using  a matrix visual and slicer with a simple masure like": 

Total = SUM(Table1[Value])

If you want to hard-code then try, this measure: 

2020 - A Total = CALCULATE( SUM(Table1[Value]) , Table1[Period] = 2020 , Table1[Type] = "a" )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[stanislav_dugas]

So far i tried to create something like this but i am getting overall number for whole column rather then for individual ID.

CALCULATE(SUM(Table1[Values]),FILTER(Table1,AND(Table1[Type]="a",Table1[Period]="2020")))

[mahoneypat · Accepted solution]

Please try this expression instead.  Also, why do you have a bi-directional relationship?  You should make that single direction.  This should work in a table visual with the ID column from your 2nd table.

NewMeasure = CALCULATE(SUM(Table1[Values], Table1[Type] = "a", Table1[Period] = "2020")

If your Period column is number type, remove the quotes on 2020.

Regards,

Pat