Forum Discussion

gmasta1129's avatar
gmasta1129
Resolver I
4 years ago
Solved

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' ),...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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
gmasta1129's avatar
gmasta1129
Resolver I
4 years ago

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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

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