cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Multiple calculations in DAX

Hello All ,

 

I have a scenario ;

 

Table A:

 

IDWEIGHTInvestment Optionvalue
10.2Stocks1
11.2Accounts1
22.2Stocks1
23.2Accounts1
24.2PPF0
35.2Stocks1

 

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

idWeight
11.2
24.2
35.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 ,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Multiple calculations in DAX

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])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2
Highlighted

Re: Multiple calculations in DAX

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])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Multiple calculations in DAX

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)

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors