cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Multiply a column by another result

Hi Guys,

 

Can you help me again please? 

 

I'm trying to gain a solution to show a % result (Margin) from a Joint venture?

 

The main data currently shows FULL margin so I wanted to add in a calculation to show the 'Would Be' margin based on the allocated joint venture %. 

 

I.e. For Company A, who has a 50% Joint venture, Total Margin=£100 so Calculated Margin should be £50. 

For Company B, who has no (100%) Joint Venture, Total Margin =£100 so Calculated Margin should be £100. 

 

I have a separate data table (Joint_Venture) that lists the different companies i.e. A, B, C D.... by the would be weighting i.e. 50%, 100%,30%100%.... etc

 

I'm guessing this would involve a DAX calulation that says  Total Margin * JV% where the JV% is 'Looked up' based on the company name? 

I hope this makes sense? Any pointers would be appreciated? 

 

Many thanks in advance for your assistance? 

 

Cheers, 

 

John

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: Multiply a column by another result

@john_whelan   Try this DAX measure

 

Margin JV = SUMX('Data','Data'[Margin CTD] * RELATED('Joint_Venture'[JV]))

 

Any questions,let me know 

 

Thanks

Nishant

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Multiply a column by another result

@john_whelan ,Can you share sample data and sample output in table format?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: Multiply a column by another result

@john_whelan Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: Multiply a column by another result

Hi,

Hopefully these screen shots will help?

Relationship.PNGData table.PNGJV table - Percentages.PNGCurrent rersult.PNG

 

All I'm looking to do is create a second results calculation.

That will be called Margin AFTER JV %

 

so for eg. Company 210 will have a result of 50% of 2,815,855.04 so will be 1,407,927.52

 

Hope this makes sense?

Highlighted
Responsive Resident
Responsive Resident

Re: Multiply a column by another result

@john_whelan   Try this DAX measure

 

Margin JV = SUMX('Data','Data'[Margin CTD] * RELATED('Joint_Venture'[JV]))

 

Any questions,let me know 

 

Thanks

Nishant

View solution in original post

Highlighted
Community Support
Community Support

Re: Multiply a column by another result

Hi @john_whelan ,

 

Try the following measure:

Margin AFTER JV % =
VAR a =
    SELECTEDVALUE ( 'Data'[Company] )
RETURN
    SUM ( Data[Margin CTD] )
        * LOOKUPVALUE ( 'Joint venture'[JV], 'Joint venture'[Company], a )

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Regular Visitor

Re: Multiply a column by another result

@Nishantjain Solution is perfect! 

 

Thanks very much!

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
Users online (827)