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,

I think you helped me a lot.  How would I calculate if I am only using two stock.  For example, a portfolio with Microsoft and facebook.  Assume Microsoft is 65% of the portfolio and facebook is 35%.  Thanks again.

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

Hi VikrantC ,

The price of Microsoft is different each day, which price do you want the portfolio use?

Best Regards,
Community Support Team _ kalyj

  • 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.