Subtract one Category value from Other Category

[AshDil]

Hi,

I have data as follows

CATEGORY	Month	SALES	TARGET
AE	March	$180,000	$200,000
AE	April	$80,000	$80,000
AE	May	$100,000	$120,000
BE	March	$210,000	$220,000
BE	April	$280,000	$165,000
BE	May	$180,000	$200,000
NA	March	$25,000	$25,000
NA	April	$10,000	$10,000
NA	May	$20,000	$20,000
UN	March	$30,000	$30,000
UN	April	$30,000	$30,000
UN	May	$30,000	$30,000

I want output as follows:

For MAR

AE,

Sales = 1,80,000 and Target = 2,00,000

NA,

Sales = 25000 and target = 25000

Now I want to subtract 'NA' sale value from 'AE' sale value same with Target.

i.e, 1,80,000 - 25,000 = 1,55,000

Please help me to do it.

Thanks,

AshDil

[v-yanjiang-msft · Accepted solution]

Hi @AshDil ,

According to your description, here's my solution.

Here's my sample data.

1. In Power Query, select Unpivot Columns, get this table:

2. Create a measure.

Measure =
IF (
    MAX ( 'Table'[CATEGORY] ) = "AE",
    MAX ( 'Table'[Value] )
        - MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[CATEGORY] = "NA"
                    && 'Table'[Month] = MAX ( 'Table'[Month] )
                    && 'Table'[Attribute] = MAX ( 'Table'[Attribute] )
            ),
            'Table'[Value]
        ),
    MAX ( 'Table'[Value] )
)

Put the measure in the matrix, get the 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.

[amitchandak]

@AshDil , You need to create measure like

calculate(sum(Table[Sales]), filter(all(Table[Category]), Table[Category] ="Na"))

or

calculate(sum(Table[Target]), filter(all(Table[Category]), Table[Category] ="AE"))

[AshDil]

Hi @amitchandak ,

With the above measures I can not show my expected output

Please help me to do it.

Thanks,

AshDil.