Question about creating Lines as totals _FRUSTRATION-

Hi quickbi 

You can maintain a disconnected table that has separate rows for each category id in each line item

And use this measure to return the value for each P&L line

line value = 
CALCULATE(
    SUM(RevenueTable[Value]),
    TREATAS(
        SELECTCOLUMNS(
            FILTER(
                RevenueLines,
                RevenueLines[Operation] = "add"
            ),
            "Category ID", RevenueLines[Category ID]
        ),
        RevenueTable[Category ID]
    )
)
-
CALCULATE(
    SUM(RevenueTable[Value]),
    TREATAS(
        SELECTCOLUMNS(
            FILTER(
                RevenueLines,
                RevenueLines[Operation] = "subtract"
            ),
            "Category ID", RevenueLines[Category ID]
        ),
        RevenueTable[Category ID]
    )
)

 

The other approach is to create a measure for each line and return the values using a condition.

SWITCH (
    SELECTEDVALUE ( 'table'[name] ),
    "Total Gross Profit", [Total GP Measure],
    "Total EBIT", [Total EBIT Measure]
)

 

hi quickbi 

 

That’s not how the table should be structured. In my example, each Category ID has its own row — they aren’t combined into a single row for each revenue line. There should also be an indicator showing whether the Category ID is an addition or a deduction. In the Query Editor, you can start by splitting the Category IDs. After that, you will need to transform the data so that each Category ID appears in its own row. Below is a sample custom column to split the category individually.

Text.Split([category id column], ",")

This will create a column with each row containing a list of values. Expand them into new rows (there will be an expand icon next to the column name). Remove trailing and preceding spaces by right click the colum and selecting Trim or Clean.