Forum Discussion

VikrantC's avatar
VikrantC
Helper I
4 years ago
Solved

CalculateTable

 I am new to Power BI and if someone can help it will be great.  I want to create three portfolios by using the following stock prices.  I want to create a CalculateTable to show the three portfolios...
Need Help
  • VikrantC's avatar
    VikrantC
    4 years ago

    Thanks.  The Portfolio of two would be 50% each.  I would use the DAX to calculate that.  I cannit use Matrix as I want to use that table.

     

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi VikrantC ,

    Actually I'm not very clear about your expected result. By my understanding, I create a table that summarizes categories by date.

    Table 2 =
ADDCOLUMNS (
    VALUES ( 'Table'[Dates] ),
    "Company",
        CONCATENATEX (
            FILTER ( ALL ( 'Table' ), 'Table'[Dates] = EARLIER ( 'Table'[Dates] ) ),
            'Table'[Company],
            ","
        ),
    "Price",
        SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[Dates] = EARLIER ( 'Table'[Dates] ) ),
            'Table'[Price]
        )
)

    Get this result.

    If this is not your expected result, could you please display the expected result in Excel.

     

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

VikrantC's avatar
VikrantC
Helper I
4 years ago

Hi Kalyj,  Here is the table I want to get.

 

The portfolio %s are same and the portfolio values changes every day.  For example,

Tech1 = Apple*0.3 + Google*0.4 + Faceboob*0.2
Tech2 = Microsoft*0.4 + Facebook*0.3+Google*0.3

 

DatesCompanyPrice
2/28/2022Microsoft$279.
3/1/2022Microsoft$288.
3/2/2022Microsoft$284.
3/3/2022Microsoft$290.
2/28/2022FaceBook$195.
3/1/2022FaceBook$199.
3/2/2022FaceBook$204.
3/3/2022FaceBook$200.
2/28/2022Apple$162.
3/1/2022Apple$158.
3/2/2022Apple$161.
3/3/2022Apple$163.
2/28/2022Google$2,638.
3/1/2022Google$2,577.
3/2/2022Google$2,377.
3/3/2022Google$2,639.
2/28/2022Tech1$2,638.
3/1/2022Tech1$2,577.
3/2/2022Tech1$2,377.
3/3/2022Tech1$2,639.
2/28/2022Tech2$2,638.
3/1/2022Tech2$2,577.
3/2/2022Tech2$2,377.
3/3/2022Tech2$2,639.
VikrantC's avatar
VikrantC
Helper I
4 years ago

Many Thanks for quick relply Kalyji.    The Tech1 and Tech2 (portfolios) can be in the same table or in new table.    I really do not want to use Power Query.   I am new to the community and also to Power BI.  I really appreciate your help.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi VikrantC ,

    According to your description, here's my solution.

    1. Create a new table.

    Table 3 =
UNION (
    SUMMARIZE ( 'Table', 'Table'[Dates], 'Table'[Company] ),
    GENERATE ( VALUES ( 'Table'[Dates] ), { "Tech1" } ),
    GENERATE ( VALUES ( 'Table'[Dates] ), { "Tech2" } )
)

    2. Create a calculated column in the new table.

    Price =
IF (
    'Table 3'[Company] IN SELECTCOLUMNS ( 'Table', "Company", 'Table'[Company] ),
    MAXX (
        FILTER (
            'Table',
            'Table'[Company] = EARLIER ( 'Table 3'[Company] )
                && 'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
        ),
        'Table'[Price]
    ),
    IF (
        'Table 3'[Company] = "Tech1",
        0.3
            * MAXX (
                FILTER (
                    'Table',
                    'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                        && 'Table'[Company] = "Apple"
                ),
                'Table'[Price]
            )
            + 0.4
                * MAXX (
                    FILTER (
                        'Table',
                        'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                            && 'Table'[Company] = "Google"
                    ),
                    'Table'[Price]
                )
            + 0.2
                * MAXX (
                    FILTER (
                        'Table',
                        'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                            && 'Table'[Company] = "FaceBook"
                    ),
                    'Table'[Price]
                ),
        IF (
            'Table 3'[Company] = "Tech2",
            0.4
                * MAXX (
                    FILTER (
                        'Table',
                        'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                            && 'Table'[Company] = "Microsoft"
                    ),
                    'Table'[Price]
                )
                + 0.3
                    * MAXX (
                        FILTER (
                            'Table',
                            'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                                && 'Table'[Company] = "Google"
                        ),
                        'Table'[Price]
                    )
                + 0.3
                    * MAXX (
                        FILTER (
                            'Table',
                            'Table'[Dates] = EARLIER ( 'Table 3'[Dates] )
                                && 'Table'[Company] = "FaceBook"
                        ),
                        'Table'[Price]
                    )
        )
    )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

     

    • VikrantC's avatar
      VikrantC
      Helper I
      4 years ago

      Hi Kalyj,

      Many thanks.  It works perfectly.

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        4 years ago

        Hi VikrantC ,

        You say previous and percentage change, did you mean for 3/1/2022 Tech1, it should be (1118-1142.8)/1118?

        Best Regards,
        Community Support Team _ kalyj