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 ,

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

  • VikrantC's avatar
    VikrantC
    Helper I
    4 years ago

    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,