cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gmasta1129
Helper III
Helper III

IF/OR Statement with Measure & Column

I created a measure which sums the column "profit/loss" by column "master portolio id"...

 

Net FX P&L2 = CALCULATE (
SUM ( 'daily_report'[profit/loss]),
FILTER (
ALLSELECTED ( 'daily_report' ),
'daily_report'[master portfolio id]
= SELECTEDVALUE ( 'daily_report'[master portfolio id] )
))

 

 

For example,

 

My data sheet contains the following columns

 

Facility Code    Master Portfolio ID     Profit/Loss                     Net FX P&L2   <-- Measure created using formula above

84151                         84151                  0                                         100

84152                         84151                50                                         100

84153                        84151                 50                                         100

 

The measure above rolls up the profit/loss (3rd column) value by the master porfolio id (2nd column). 

 

However, I also need the facility codes (1st column) 84152 and 84153 be 0 as they should not contain 100. The 100 profit and loss value should only be in the Facility code 84151 as that is the main facility. 

 

I tried to use an IF/OR statement but cannot when Facility Code is a column and Net FX P&L2 is a measure.  Also, I have about 5 other facility codes with this issue.    Can someone please help? 

 

Facility Code    Master Portfolio ID     Profit/Loss                     Net FX P&L2   <-- What the Net FX P&L2 column should look like

84151                         84151                  0                                         100

84152                         84151                50                                         0

84153                        84151                 50                                         0

 

                                       

 

 

 

 

Thank you!  

1 ACCEPTED SOLUTION

Hi,

Thank you for your message.

Could you please try something like below?

 

Net FX P&L2 Sample Data 2 measure: =
VAR _masterid =
    MAX ( SampleData2[Master Portfolio ID] )
VAR _currentdate =
    MAX ( SampleData2[Run Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER (
                SampleData2,
                SampleData2[Facility Code] = SampleData2[Master Portfolio ID]
            ),
            "@expectedmeasure",
                CALCULATE (
                    SUMX (
                        FILTER (
                            ALL ( SampleData2 ),
                            SampleData2[Master Portfolio ID] = _masterid
                                && SampleData2[Run Date] = _currentdate
                        ),
                        SampleData2[Profit/Loss]
                    )
                )
        ),
        [@expectedmeasure]
    ) + 0

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
gmasta1129
Helper III
Helper III

@Jihwan_Kim 

Worked perfectly.  Thank you for your help! 🙂

gmasta1129
Helper III
Helper III

Hello @Jihwan_Kim,

 

Thank you for the response.  After copy and pasting the code above, I received a larger number.  I should have mentioned that the report is being filtered (slicer) by a column titled "Run Date" which contains date data (report runs on a daily basis).  I am not sure if this is why I am seeing a large number because it is summing all the dates together? Just added this column to the chart below for reference.  

 

 

Run Date          Facility Code    Master Portfolio ID     Profit/Loss            Net FX P&L2   

 7/11/2022            84151                       84151                  0                            100

 7/11/2022            84152                       84151                  50                          100

 7/11/2022            84153                        84151                 50                          100

 7/8/2022             84151                       84151                  0                            160

 7/8/2022            84152                       84151                  80                           160

 7/8/2022            84153                        84151                 80                          160

Hi,

Thank you for your message.

Could you please try something like below?

 

Net FX P&L2 Sample Data 2 measure: =
VAR _masterid =
    MAX ( SampleData2[Master Portfolio ID] )
VAR _currentdate =
    MAX ( SampleData2[Run Date] )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER (
                SampleData2,
                SampleData2[Facility Code] = SampleData2[Master Portfolio ID]
            ),
            "@expectedmeasure",
                CALCULATE (
                    SUMX (
                        FILTER (
                            ALL ( SampleData2 ),
                            SampleData2[Master Portfolio ID] = _masterid
                                && SampleData2[Run Date] = _currentdate
                        ),
                        SampleData2[Profit/Loss]
                    )
                )
        ),
        [@expectedmeasure]
    ) + 0

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pibx file like below.

It is for creating a measure.

 

Picture1.png

 

Net FX P&L2 measure: = 
VAR _masterid =
    MAX ( Data[Master Portfolio ID] )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( Data, Data[Facility Code] = Data[Master Portfolio ID] ),
            "@expectedmeasure",
                CALCULATE (
                    SUMX (
                        FILTER ( ALL ( Data ), Data[Master Portfolio ID] = _masterid ),
                        Data[Profit/Loss]
                    )
                )
        ),
        [@expectedmeasure]
    ) + 0

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


amitchandak
Super User
Super User

@gmasta1129 , Try like

 


Net FX P&L2 = CALCULATE (
SUM ( 'daily_report'[profit/loss]),
FILTER (
ALLSELECTED ( 'daily_report' ),
'daily_report'[master portfolio id]
= max ( 'daily_report'[master portfolio id] ) && 'daily_report'[Facility ID] = minx(filter('daily_report'[master portfolio id]
= max ( 'daily_report'[master portfolio id] ) ) 'daily_report'[Facility ID])
))

 

or

 

Net FX P&L2 = CALCULATE (
SUM ( 'daily_report'[profit/loss]),
FILTER (
( 'daily_report' ),
'daily_report'[Facility ID] = minx(filter('daily_report'[master portfolio id]
= max ( 'daily_report'[master portfolio id] ) ) 'daily_report'[Facility ID])
))

 

 

refer

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

 

You need first means Min

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors