Calculate UID wise

[Anonymous]

Hi Team,

I need your help, regarding below column formulas,

1) Remaing UID Stock - > When the UID's are same then Prod_Total should be summed up and at the same time, the difference between the "Prod_Total" and "stock" of each "category" should be found.
And this should work based on dynamic slicer. eg: if slicer is Oct month then Prod_Total should be calculated from Jan to Oct .
  

Note: Prod_Total is Measures (calculates new measure)

Thanks,

KV's

[AllisonKennedy]

Sorry, you have covered up UID - which rows are they the same for? How many tables is your raw data? This could be done with the right table visual or ifyou create a measure:

UID Prod Total = CALCULATE(SUM{Table[Prod_Total]), ALL{Table[Category]))

Then subtract that from stock in another or same measure. I'm just not sure how many tables you have and how they are related, so that may change things slightly.

[Anonymous]

Hi @AllisonKennedy ,

[AllisonKennedy]

@Anonymous 

You should be able to do as a new measure:

Remaining UID Stock = MAX(Table[Stock]) - CALCULATE(SUM(Table[Prod_Total]), ALL(Table[Category]))

or as a new COLUMN:

Remaining UID Stock = Table[Stock] - SUMX(FILTER(ALL(Table), Table[UID] = EARLIER(Table[UID])), Table[Prod_Total])

[Anonymous]

[amitchandak]

@Anonymous , try like

remaining UID Stock = sum(Table[stock])- calculate(sum(Table[Prod_Total]) , allexcept(Table, Table[UID]))
or
remaining UID Stock = calculate(sum(Table[Prod_Total]) , allexcept(Table, Table[UID])) -sum(Table[stock])

[Anonymous]

Hi @amitchandak , how it will work with dynamic slicer (Date table, UID table separate).

[amitchandak]

@Anonymous , not very clear. to me it seem like

remaining UID Stock = sum(Table[Prod_Total]) -sum(Table[stock])

remaining UID Stock PO = if([remaining UID Stock]<0, 0, [remaining UID Stock])

[Anonymous]

Hi @amitchandak ,

I'm expected below result,

[Anonymous]

Hi @amitchandak ,

expected based on UID (same ID) prod_total subtract by each Category row wise..

Thanks,

KV's