Multiple calculations in DAX

[ANKITBISANI]

Hello All ,

I have a scenario ;

Table A:

ID	WEIGHT	Investment Option	value
1	0.2	Stocks	1
1	1.2	Accounts	1
2	2.2	Stocks	1
2	3.2	Accounts	1
2	4.2	PPF	0
3	5.2	Stocks	1

Table B: Table B consist of Max Weight group by Id.

id	Weight
1	1.2
2	4.2
3	5.2

If I select options Stocks , Accounts .

SQL 1. : Select Sum(weight) from table B where ID in (select distinct ID from Table A where Value =1  and Investment Option = 'Stocks , Accounts')

***Investment option would be drived from slicer

SQL 2 : Select Sum(Weight) from table B

measure = SQL1 Result / SQL 2 result

How would this measure can be achived through DAX .

Regards ,

[MattAllington · Accepted solution]

Your post is a little confusing, but let me try

load both tables

Join the tables on the ID column

write these measure. 

Sql 1 = calculate(sum(tableA[weight],tableA[value]=1)

sql 2 = sum(tableB[weight])

measure = divide([sql 1],[sql 2])

[ANKITBISANI]

Hello @MattAllington ,

Thanks that worked with little tweak as below :

As I wanted max , I took the values from table B and rest other are the same .

Sql 1 = calculate(sum(tableB[weight],tableA[value]=1)