Subtraction Row wise Calculation

[Anonymous]

Hi Team,

DAX - based on UID (Unique) total sum value subtraction with each UID Category like row wise, could you please help me.

Note:

1) stock and sal_total both are measures value (calculated new measures)

2) Blac_Manual -> expected result (Manual Calculation 

3) Cat -> row wise subtract UID sum of total like first ex : GH-258 Stock is 14 - 5 (UID sum) = expecting result is 9

formul Not working : 

balc = sumx(filter(Sheet2, [UID] = earlier([UID]) ),[Stock]) - maxx(filter(Sheet2, [UID] = earlier([UID]) ),[Production])

Thanks,

KV's

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

According to your description, I create this data：

Here are the steps you can follow：

1. Create measure.

Stock: Cat category value when uid is consistent

stock =
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Cat]=MAX('Table'[Cat])))

sal_total：Sum by [cat] class UID

sal_total =
var _UID=CALCULATE(MAX('Table'[UID]),FILTER(ALL('Table'),[Cat]=MAX('Table'[Cat])))
return
CALCULATE(COUNT('Table'[UID]),FILTER(ALL('Table'),'Table'[UID]=_UID))

Blac_Manual：Expected results

Balc_Manual = [stock]-[sal_total]

2. Result

You can downloaded PBIX file from here.

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Logic is not very clear.

Create a new measure like

balc = sumx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Stock]) - maxx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Production])

[Anonymous]

Hi @amitchandak ,

It's working DIM based total sum, but I'm expecting DIM while including Category then total volume split like below,