MODEL DATA ISSUE

[ranibosch]

Good day,

I have a model with the following 2 tables:

1. Item consumption entries:

Year	Month	Responsibility Center	Item No	BU	Quantity
2017	Mar	WR	ALM001	AC	- 247,20
2017	Apr	WR	ALM001	AC	- 185,40
2017	May	WR	ALM001	AC	- 275,24
2017	May	WR	ALM001	AC	- 13,16
2017	Feb	WR	CAS001	AC	- 309,00
2017	Apr	WR	CAS001	AC	- 124,52
2017	May	WR	CAS001	AC	- 257,50
2017	Jun	WR	CAS001	AC	- 125,63
2017	Oct	WR	CAS001	AC	- 453,60
2017	Nov	WR	CAS001	AC	453,60

2. Item remaining balance:

Item No	Responsibility Center	Warehouse Stock Balance
CAS001	WR	680,4
CAS001	WR	3560,76
ALM001	WR	8527,68
ALM001	WR	9752,4
CAS001	WR	15581,16
ALM001	WR	15785,28
ALM001	WR	16261,56
ALM001	WR	16556,4

From the abovementioned tables/data, I would like to get the following results:

**note that some calculation will happen on a pivot table as expression**

          TABLE 1                                                                                                    TABLE 2

[shiveshsingh]

Can you share the app? what expression have you used for calculation.

If you want that expression values doesn't change on month selection, you can bypass month in expression

like {<Month>}

[ranibosch]

Hi Shivesh

please find attached the test model.

I've made the selection as well. You'll see that there will not be any results then.

[PrashantSangle]

Try below expression.

(Sum (Quantity)/6)*-1

/

Sum (TOTAL<[Responsibility Center]> {<Year=,Month=>}[Warehouse Stock Balance])

If it is not helping you please share base file with formula.

Regards,

[shiveshsingh · Accepted solution]

Try this,

=(Sum (Quantity)/6)*-1

/

Sum ({<Year,Month>}[Warehouse Stock Balance])

If your results are not correct, let me know the logic of your output.