Forum Discussion
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
john_whelan Try this DAX measure
Margin JV = SUMX('Data','Data'[Margin CTD] * RELATED('Joint_Venture'[JV]))
Any questions,let me know
Thanks
Nishant
6 Replies
john_whelan ,Can you share sample data and sample output in table format?
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.Hi,
Hopefully these screen shots will help?
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?
john_whelan Try this DAX measure
Margin JV = SUMX('Data','Data'[Margin CTD] * RELATED('Joint_Venture'[JV]))
Any questions,let me know
Thanks
Nishant
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 )
