how to subtract 2 data grids and get the result

[dilipkumar]

i created a report using matric . below is the report.

i want a result set  some thing like below .  (Rev  - COGS by each line item )

Col1        col2               value  (CALC)

Subsidy   Sales            (RevSales - Cogs Sales)  (1579-1590)

                Auction        (RevAuction -COGS Auction)  (94474 -80504)

[v-shex-msft]

HI @dilipkumar,

Can you please share some dummy data with the same data structure as your original table for test? For your requirement, I think they need to do some transform on table structure by Dax formula or M query.

Regards,

Xiaoxin Sheng

[Anonymous]

An easy way to solve this is using a few measures in a row.  I'll show you how to create the "SALES" version, then you can create an equivelent for "Auction"

Step 1 is to create a basic "Sales" measure.  This will be something like

SALES = SUM('YourTable'[SalesAmount])

Step 2 is to create new measures that are pre-filtered to specific types of sales, using the Rev_Cogs column.  This will look like

REV Sales = CALCULATE(
	[SALES],
	'YourTable'[Rev_Cogs] = "Rev"
)

COGS Sales = CALCULATE(
	[SALES],
	'YourTable'[Rev_Cogs] = "COGS"
)

Step 3 is to make your final measure which will do the subtraction.  Something like:

Subsidy Sales = [REV Sales] - [COGS Sales]

[dilipkumar]

so far so good. i was able to attain that. thanks you Ross. but if see here for line  eg ( allocations for rev) - (allocation for Cogs) i get the cal correct but the label should not be "Allocations" it should be "Net Allocations"  so do "Net Eqipment  incuurec"  and "net Equipment Allocated" ...

[Anonymous]

That looks like its coming directly out of your data.  If there is an issue in the data itself, you'll need to correct it.  This could be done as part of the import process.