How to ignore a dimension while using SUM

[VolkanKu]

Hello,

I am trying to make a report but i got stuck at getting a sum while ignoring one of the dimensions at the chart.

What i am trying to report is the top 5 materials for each period(Year, Month or Week if can use alternative or just Month if i cant) Rework Quantity of material in period / Total Stock Quantity for the period.

There are also a lot of other filters in the app from various tables that are joined by Material column. Is there any way to omit a dimension while using SUM function or can i get the result by using another function?

The main data i am using is like these 2 tables. Also the end result i need looks like the picture at the end, with ratios replacing material codes.  

TABLEX				TABLEY				TableZ
Material	Date	Stock Quantity		Material	Date	Rework Quantity		Material	Custpartnumber
A	01.10.2019	36865		A	01.10.2019	127		A	AAAA
C	01.10.2019	141		C	01.10.2019	19		B	BBBB
A	01.09.2019	103		A	01.09.2019	106		C	CCCC
B	01.09.2019	390		B	01.09.2019	27		D	DDDD
C	01.09.2019	133		C	01.09.2019	10		E	EEEE
A	01.08.2019	56		A	01.08.2019	6
B	01.08.2019	552		B	01.08.2019	256
C	01.08.2019	28		C	01.08.2019	47
A	01.07.2019	148		A	01.07.2019	6
B	01.07.2019	16		B	01.07.2019	336
A	01.07.2019	362		A	01.07.2019	32
A	01.06.2019	90		A	01.06.2019	1
B	01.06.2019	188		B	01.06.2019	209
C	01.06.2019	285		C	01.06.2019	11
D	01.06.2019	2865		D	01.06.2019	81
E	01.06.2019	448		E	01.06.2019	81

The results i am trying to get are like these below, month year as dimension and year as alternative dimension.

Month Year						Year
Material (1)	Date (2)	Total Stock Quantity for the period (3)	Rework Quantity of material in period (4)	Rework Ratio (4/3)		Material (1)	Date (2)	Total Stock Quantity for the period (3)	Rework Quantity of material in period (4)	Rework Ratio (4/3)
A	01.10.2019	37006	127	0,34%		A	2019	42670	278	0,65%
C	01.10.2019	37006	19	0,05%		C	2019	42670	87	0,20%
A	01.09.2019	626	106	16,93%		A	2019	42670	278	0,65%
B	01.09.2019	626	27	4,31%		B	2019	42670	828	1,94%
C	01.09.2019	626	10	1,60%		C	2019	42670	87	0,20%
A	01.08.2019	636	6	0,94%		A	2019	42670	278	0,65%
B	01.08.2019	636	256	40,25%		B	2019	42670	828	1,94%
C	01.08.2019	636	47	7,39%		C	2019	42670	87	0,20%
A	01.07.2019	526	38	7,22%		A	2019	42670	278	0,65%
B	01.07.2019	526	336	63,88%		B	2019	42670	828	1,94%
A	01.07.2019	526	38	7,22%		A	2019	42670	278	0,65%
A	01.06.2019	3876	1	0,03%		A	2019	42670	278	0,65%
B	01.06.2019	3876	209	5,39%		B	2019	42670	828	1,94%
C	01.06.2019	3876	11	0,28%		C	2019	42670	87	0,20%
D	01.06.2019	3876	81	2,09%		D	2019	42670	81	0,19%
E	01.06.2019	3876	81	2,09%		E	2019	42670	81	0,19%

[Kashyap_R]

hi

try like this

Sum({<[Dimension]=>}Value)

this will nullify the filters applied on those dimensions

Hope this helps

Thanks 

[VolkanKu]

Hello,

Thanks for the fast reply.

I have tried using the formula you posted like this;

Sum({<[TableZ.custpartnumber]=>}[TableX.quantity]) but no success 😞 

[JordyWegman]

Hi Volkan,

Can you give a sketch of the outcome? This would make it a lot easier.

Jordy

Climber

[VolkanKu]

Hello,

I have edited the post to include the desired results 🙂