Table Visualization Line Calculations

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:

 

 

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!