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 with date in the first column, Portfolio in the second column, and portfolio valve in the 3rd column (the Portfolios can be in individual rows if it is easier).    For example, portfolio A is Microsoft(50pct) and Apple(50pct).  I want to do this in DAX (not in power Query).  Eventually, I want to measure portfolio performance against each of the individual companies.

DatesCompanyPrice
2/28/2022Microsoft$279.0
3/1/2022Microsoft$288.0
3/2/2022Microsoft$284.0
3/3/2022Microsoft$290.0
2/28/2022FaceBook$195.0
3/1/2022FaceBook$199.0
3/2/2022FaceBook$204.0
3/3/2022FaceBook$200.0
2/28/2022Apple$162.0
3/1/2022Apple$158.0
3/2/2022Apple$161.0
3/3/2022Apple$163.0
2/28/2022Google$2,638.0
3/1/2022Google$2,577.0
3/2/2022Google$2,377.0
3/3/2022Google$2,639.0

 

Thanks VC

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.

11 Replies

  • 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

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    VikrantC I have several questions...

     

    First, what do you mean when you say "I want to create a CalculateTable to show the three portfolios...".  Are you saying you want to create three table or matrix visualizations?  If so, CALCULATETABLE is completely unnecessary for this.

     

    Second, you mention portfolio value.  It's impossible to have portfolio value with the data you shared.  You have stock prices but no quantities.  You might be able to assume the same number of shares given you said 50/50 split between Microsoft and Apple, but my inner accountant is screaming quantity!

     

    Third, how are you measuring portfolio performance?

    • VikrantC's avatar
      VikrantC
      Helper I
      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.