DAX Calculation: multiple tables

[esingh]

Hi Fabric Community,

I am new to Power BI and facing issues to create a dax calculation to achieve the following result:

From table 1 for insitution ABC, id 10, header string='A' we need to pick out responsecount=14012333 and divide it by values from the table 2 with following inputs institution ABC, Sum of values for Optionstring= 'Direct Costs' and 'Indirect Costs" and headerstring='aa' and 'bb'  which means= 46 dollars( 13+16+4+13) refered filtered table 2

I am looking for the values of  1401233/46.

Table1:

Table 2:

Filtered Table2:

Thanks

Ekam

[ryan_mayu]

pls paste the sample data here (not the screenshot). What about the rest ,e.g. DEF? also get the value from table 2 with only "aa" and "bb" in the Direct Costs' and 'Indirect Cost"?

[esingh]

Hi Ryan Mayu, You are right in your interpretation we need to get the data for DEF as well. I have just kept data of two institutes in table 2 for the purpose. Filter values remain unchanged, institute changes(dynamic). Sample data.

id	optionString	headerString	responseCount	institution_name	institution_code
10	xx	A	1401233	ABC	10
10	xx	A	1401000	DEF	20
10	yy	A	0	GHI	30
10	yy	A	0	JKL	40
10	xx	B	59	ABC	10
10	xx	B	53	DEF	20
10	yy	B	0	GHI	30
10	yy	B	0	JKL	40

Table 2:
id	optionString	headerString	responseCount	institution_name	institution_code
4	Expenditures	aa	$15	ABC	10
4	Expenditures	bb	$18	ABC	10
4	Expenditures	cc	$12	ABC	10
4	Direct Costs	aa	$13	ABC	10
4	Direct Costs	bb	$16	ABC	10
4	Direct Costs	cc	$11	ABC	10
4	Indirect Costs	aa	$4	ABC	10
4	Indirect Costs	bb	$13	ABC	10
4	Indirect Costs	cc	$2	ABC	10
4	Assets	aa	$10	ABC	10
4	Assets	bb	$12	ABC	10
4	Assets	cc	$8	ABC	10
4	Labor Expense	aa	$20	ABC	10
4	Labor Expense	bb	$64	ABC	10
4	Labor Expense	cc	$13	ABC	10
4	Expenditures	aa	$15	DEF	20
4	Expenditures	bb	$18	DEF	20
4	Expenditures	cc	$12	DEF	20
4	Direct Costs	aa	$13	DEF	20
4	Direct Costs	bb	$16	DEF	20
4	Direct Costs	cc	$11	DEF	20
4	Indirect Costs	aa	$4	DEF	20
4	Indirect Costs	bb	$13	DEF	20
4	Indirect Costs	cc	$2	DEF	20
4	Assets	aa	$10	DEF	20
4	Assets	bb	$12	DEF	20
4	Assets	cc	$8	DEF	20
4	Labor Expense	aa	$20	DEF	20
4	Labor Expense	bb	$64	DEF	20
4	Labor Expense	cc	$13	DEF	20

[ryan_mayu]

@esingh 

pls try this

Measure = DIVIDE(sum('Table'[responseCount]), SUMX(FILTER('Table (2)','Table (2)'[institution_name]=MAX('Table'[institution_name])&& 'Table (2)'[optionString] in {"Indirect Costs","Direct Costs"} && 'Table (2)'[headerString] in {"aa","bb"}),'Table (2)'[responseCount]))

pls see the attachment below

[BIswajit_Das]

Hello @esingh 
As per your explanation & requirement you can try the below DAX but There might be a problem afterwards cause as you explained all the filter values are hardcoded in to the DAX.
It Means This DAX only work for mentioned filters It means the values are specified.

Thanks & Regards...

[esingh]

Tanks Biswajit , But the data need to vary based on the insitute. filter remain fixed.

Ekam