In DAX how to filter table1 value&sum is calculated only if table2 boolen value set during period

[ohman]

Hi all!

I've been few days already tried sort this issue with no success (not even from chat GPT;)

I have an action table2, from which I try to use trigger "sold" to calculate dividents from another table1 only if stock sold on same period. I have linked tables stocks, dates

table2

dates	type	stock	price
1.1.2022	sold	alpha	100
11.1.2022	buy	beeta	200
21.1.2022	buy	gamma	300
1.1.2023	sold	beeta	100
11.1.2023	sold	alpha	300

and table1 I use to sum dividents in various way

dates	stock	dividen
21.1.2022	alpha	10
31.1.2022	beeta	20
10.2.2022	gamma	30
20.2.2022	zorro	20
2.3.2022	zeta	20
21.1.2023	alpha	10
21.1.2023	beeta	20
31.1.2023	gamma	30
10.2.2023	zorro	20
20.2.2023	zeta	20

In BI I can summarize OK all stocks "sold" and dividends in given period, but calculating in another measure dividents of only sold stocks fails

year	stock	sold value	all dividents	sold dividents
2022 sum		100	100	this sum dont work
2022	alpha	100	10	10
2022	beeta		20
2022	gamma		30
2022	zorro		20
2022	zeta		20
2023 sum		200		this sum dont work
2023	alpha	300	10	10
2023	beeta	100	20	20
2023	gamma		30
2023	zorro		20
2023	zeta		20

I manage to create in table2 a boolean if stock sold, but do not know how to apply it properly. E.g. the sum of sold stocks dividents is either empty or sums erroneously all sold stocks dividents during all years.

e.g. to count sum of sold stocks I use: soldprice= CALCULATE(SUM('table1'[price]),filter(all('table1[Type]),('table1'[Type]="sold")))

[Greg_Deckler]

@ohman Not sure, but first, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[ohman]

Thanks Greg for long list of reading, I seem to be too inexperienced with BI. I cannot get none of these working, I trieed to create shareable simple BI with my sample data but got stuck  with other basics before able to try solutions. I tried in my original data which somewhat works , but only simple m_single formula worked Measure Totals, The Final Word - Microsoft Power BI Community

another suggestion Dealing with Measure Totals - Microsoft Power BI Community I added in my sample excel and BI but again the key formula "MyMeasure2" failed, has BI changed that much sionce 2016 POSTED?

but since cannot upload files probably too difficult explain, I need re-take all trainings I took years back and forgot everything as did not apply for long time