Table Visualization Line Calculations

[Anonymous]

Is it possible to insert into a table visualization a row with a calculation based on other row values? 

                             Current Period       Data Source

Sales                 $1,000                          Data table

Gross Profit     $200                          Data table

Expenses           $150                          Data table

Profit                      $50                          Calculation = Gross Profit $200 minus Expenses $150

Profit %                    5%                         Calculation = Profit $50 divided by Sales $1000

If yes, then I need some instruction.

Thanks.

Tim

[v-kelly-msft]

Hi @Anonymous ，

You could also create a calculated table as below:

New table =
VAR _profit =
    CALCULATE (
        SUM ( 'Table'[Current Period  ] ),
        FILTER ( 'Table', 'Table'[category] = "Gross Profit" )
    )
VAR _expense =
    CALCULATE (
        SUM ( 'Table'[Current Period  ] ),
        FILTER ( 'Table', 'Table'[category] = "Expenses" )
    )
VAR _sales =
    CALCULATE (
        SUM ( 'Table'[Current Period  ] ),
        FILTER ( 'Table', 'Table'[category] = "Sales" )
    )
RETURN
    UNION (
        SELECTCOLUMNS (
            'Table',
            "category", 'Table'[category],
            "Current Period", FORMAT ( 'Table'[Current Period  ], "$#,##0" )
        ),
        ROW (
            "category", "Profit",
            "Current Period", FORMAT ( _profit - _expense, "$#,##0" )
        ),
        ROW (
            "category", "Profit%",
            "Current Period", FORMAT ( DIVIDE ( _profit - _expense, _sales ), "percent" )
        )
    )

And you will see:

 For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hello Kelly, I am encountering an error when I set up the calculated new table using my data.  Any suggestions?

Thanks for the sharing this approach.

Tim

[v-kelly-msft]

Hi @Anonymous ,

Is your issue solved now?

If so,could you pls mark the reply as answered to close it?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft]

Hi  @Anonymous ,

Cant tell why by the screenshot.

Could you pls share me your .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[DataInsights]

@Anonymous,

Try this solution.

1. Create a disconnected table (no relationships) with custom labels to be displayed in the table visual. Sort the Label column by the Index column (this controls the display order in the table visual).

2. Create measure Display Amount. It uses measures such as Sales, which can be defined in various ways depending on your data model (could be based on Account number, Category, etc.).

Display Amount = 
SWITCH (
    SELECTEDVALUE ( CustomLabel[Label] ),
    "Sales", FORMAT ( [Sales], "$#,##0;($#,##0)" ),
    "Gross Profit", FORMAT ( [Gross Profit], "$#,##0;($#,##0" ),
    "Expenses", FORMAT ( [Expenses], "$#,##0;($#,##0" ),
    "Profit", FORMAT ( [Gross Profit] - [Expenses], "$#,##0;($#,##0" ),
    "Profit %", FORMAT ( DIVIDE ( [Gross Profit] - [Expenses], [Sales] ), "Percent" )
)

Sales = CALCULATE ( SUM ( Ledger[Amount] ), Ledger[Category] = "Sales" )

3. Create visual: