Calculation at Row Level

[rajibmahmud]

Can someone suggest how can I have Value calculated in a new measure at Row which is not part of Datatable.

Below is source table:

Product	Measure	Month	Value
A	Sales	Jan	100
A	Gross Profit	Jan	60
A	Operating Profit	Jan	40
A	Sales	Feb	200
A	Gross Profit	Feb	120
A	Operating Profit	Feb	90
A	Sales	Mar	300
A	Gross Profit	Mar	170
A	Operating Profit	Mar	120

I need to calculate Gross Margin & OP% from the table and show in below format.

	Jan	Feb	Mar
Sales	100	200	300
Gross Profit	60	120	170
Operating Profit	40	90	120
Gross Margin	60.0%	60.0%	56.7%
OP%	40.0%	45.0%	40.0%

[v-yulgu-msft]

Hi @rajibmahmud,

You could try this workaround.

First, add two calculated columns in source table. (In my test, it's Table-2)

Gross Margin =
IF (
    'Table-2'[Measure] = "Gross Profit",
    (
        CALCULATE (
            SUM ( 'Table-2'[Value] ),
            ALLEXCEPT (
                'Table-2',
                'Table-2'[Product],
                'Table-2'[Measure],
                'Table-2'[Month]
            )
        )
            / CALCULATE (
                SUMX ( FILTER ( 'Table-2', 'Table-2'[Measure] = "Sales" ), 'Table-2'[Value] ),
                ALLEXCEPT ( 'Table-2', 'Table-2'[Product], 'Table-2'[Month] )
            )
    ),
    BLANK ()
)

OP% =
IF (
    'Table-2'[Measure] = "Operating Profit",
    (
        CALCULATE (
            SUM ( 'Table-2'[Value] ),
            ALLEXCEPT (
                'Table-2',
                'Table-2'[Product],
                'Table-2'[Measure],
                'Table-2'[Month]
            )
        )
            / CALCULATE (
                SUMX ( FILTER ( 'Table-2', 'Table-2'[Measure] = "Sales" ), 'Table-2'[Value] ),
                ALLEXCEPT ( 'Table-2', 'Table-2'[Product], 'Table-2'[Month] )
            )
    ),
    BLANK ()
)

Then, create a new table.

Table_3 =
UNION (
    SELECTCOLUMNS (
        'Table-2',
        "Measure", 'Table-2'[Measure],
        "Month", 'Table-2'[Month],
        "Value", 'Table-2'[Value]
    ),
    SELECTCOLUMNS (
        'Table-2',
        "Measure", "Gross Margin",
        "Month", 'Table-2'[Month],
        "Value", 'Table-2'[Gross Margin]
    ),
    SELECTCOLUMNS (
        'Table-2',
        "Measure", "OP%",
        "Month", 'Table-2'[Month],
        "Value", 'Table-2'[OP%]
    )
)

You can use a matrix to display data from Table_3.

Regards,
Yuliana Gu

[Phil_Seamark]

Hi @rajibmahmud

I'd recommend pivoting your 2nd table to be a format that looks more like this.  Use the filter and unpivot functions in the query editor to help.  Then you can create DAX measures (or merge the tables in the Query Editor)

Gross Profit    Jan      60
Gross Profit    Feb     120
Gross Profit    Mar     170
Op. Profit      Jan      40
Op. Profit      Feb      90
Op. Profit      Mar     120