Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I created a matrix (portfolio code is the first column) and trying to figure out a formula to sum up the profit and loss column by Master portfolio code. the columns are as follows...
For ex: Port Code 71111 and 71112 roll up into the same Master Port Code of 71111. I need the profit and loss for 71111 and 71112 summed together (100,000+200,000) = 300,000
Solved! Go to Solution.
Hi @gmasta1129 ,
You can create a measure as below to get the [Net Profit and Loss], and the value will be changed dynamically base on the slicer date selections.... Please find the details in the attachment.
Net Profit and Loss =
CALCULATE (
SUM ( 'Table'[Profit and Loss] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Master Portfolio Code]
= SELECTEDVALUE ( 'Table'[Master Portfolio Code] )
)
)
Best Regards
Hello,
Thank you for the responses. I tried both formula's and they did not work. I forgot to mentioned that i have a column labeled Run Date which is being used a a slicer. Would i need to add this to the formula? I need the net profit and loss by date.
See result below using @YukiK formula
Hi @gmasta1129 ,
You can create a measure as below to get the [Net Profit and Loss], and the value will be changed dynamically base on the slicer date selections.... Please find the details in the attachment.
Net Profit and Loss =
CALCULATE (
SUM ( 'Table'[Profit and Loss] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Master Portfolio Code]
= SELECTEDVALUE ( 'Table'[Master Portfolio Code] )
)
)
Best Regards
Here is the code
NetProfitAndLoss =
VAR SelectedMasterPortfolioCode =
SELECTEDVALUE ( Sampledata[Master PortfolioCode] )
RETURN
CALCULATE (
SUM ( Sampledata[Profit and Loss] ),
FILTER (
ALL ( Sampledata ),
Sampledata[Master PortfolioCode] = SelectedMasterPortfolioCode
)
)
I would create a measure like this:
Sum of Profit and Loss Per Master Code =
CALCULATE(
SUM (MyTable[profit and loss]),
ALLEXCEPT( MyTable, MyTable[master code] )
)
Hope this helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |