cancel
Showing results for
Did you mean:
Contributor III

Calculate Sum Basis Data Availability for another Dimension

Hi Team,

I have two tables .

Table 1) Sales Value of two products available for different period (Clean X untill Jun and Battery untill Apr).

Table 2) Has the cost for both these products but available untill Dec .

Question is how do i calcuate the Profit (Sales-Cost)  for the period only untill sales value is available for each product.

Sample Table below:

 Month Country Products Sales Month Country Products Cost Jan UKI Clean X 10,959,685 Jan UKI Clean X 10,959,685 Feb UKI Clean X 5,672,042 Feb UKI Clean X 5,672,042 Mar UKI Clean X 7,903,795 Mar UKI Clean X 7,903,795 Apr UKI Clean X 4,061,328 Apr UKI Clean X 4,061,328 May UKI Clean X 6,208,527 May UKI Clean X 6,208,527 Jun UKI Clean X 3,067,256 Jun UKI Clean X 3,067,256 Jan USA Battery 10,959,685 Jul UKI Clean X 10,959,685 Feb USA Battery 5,672,042 Aug UKI Clean X 5,672,042 Mar USA Battery 7,903,795 Sept UKI Clean X 7,903,795 Apr USA Battery 4,061,328 Oct UKI Clean X 4,061,328 Nov UKI Clean X 3,067,256 Dec UKI Clean X 10,959,685 Jan UKI Battery 10,959,685 Feb UKI Battery 5,672,042 Mar UKI Battery 7,903,795 Apr UKI Battery 4,061,328 May UKI Battery 6,208,527 Jun UKI Battery 3,067,256 Jul UKI Battery 10,959,685 Aug UKI Battery 5,672,042 Sept UKI Battery 7,903,795 Oct UKI Battery 4,061,328 Nov UKI Battery 3,067,256 Dec UKI Battery 10,959,685

Labels (1)
• General Question

2 Replies
MVP

If these are two separate data tables, and the identical-name fields function as keys, and there is always a value for every month up until the last one (so, if the last month is November, May can't be missing), you could get away with just a simple sum:

Sum(Sales - Cost)

This is because any time the Sales part of the equation is missing (null), then Sales - Cost is null. I've demonstrated using a partial, simplified dataset below - note that the 1000 cost for Battery in month 3 does not factor into the result on the left.

If your data doesn't match this description and this was an oversimplified example, please elaborate on the actual structure as another solution may be required.

Contributor III
Author

I am computing the profit in a KPI box. So by default without any filters,

"the sum of sales" - "sum of cost only untill the period sales is available" is what i am looking for. And this may vary from product to product. therefore in the kpi box, the cost shoudl be taken only for those periods for which the sales is available for each product. i hope i am elaborative now.