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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
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.