CalculateTable

[VikrantC]

 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.

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

Thanks VC

[VikrantC · Accepted solution]

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 · Accepted solution]

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]

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]

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]

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

Dates	Company	Price
2/28/2022	Microsoft	$279.
3/1/2022	Microsoft	$288.
3/2/2022	Microsoft	$284.
3/3/2022	Microsoft	$290.
2/28/2022	FaceBook	$195.
3/1/2022	FaceBook	$199.
3/2/2022	FaceBook	$204.
3/3/2022	FaceBook	$200.
2/28/2022	Apple	$162.
3/1/2022	Apple	$158.
3/2/2022	Apple	$161.
3/3/2022	Apple	$163.
2/28/2022	Google	$2,638.
3/1/2022	Google	$2,577.
3/2/2022	Google	$2,377.
3/3/2022	Google	$2,639.
2/28/2022	Tech1	$2,638.
3/1/2022	Tech1	$2,577.
3/2/2022	Tech1	$2,377.
3/3/2022	Tech1	$2,639.
2/28/2022	Tech2	$2,638.
3/1/2022	Tech2	$2,577.
3/2/2022	Tech2	$2,377.
3/3/2022	Tech2	$2,639.

[VikrantC]

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]

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]

Hi Kalyj,

Many thanks.  It works perfectly.

[v-yanjiang-msft]

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

[VikrantC]

Hi,  

I am sorry, I was out of town.  Yes, (1,118 - 1142)/1118.  Also how does the formula vary on weekly, monthly, quarterly or annual basis based on a "Date" table.

Thanks,

[littlemojopuppy]

@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 · Accepted solution]

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.