Calculates values in a PivotTable

[ttal7878]

Hi guys,

I'm trying to calculate the total amount of money that the company has spent on leads,

by suppliers that the company works with,by day,by country and by product.

In order to display so, I used a pivot table with 2 Cyclic groups,

The problem is:

When I filter: Day=1

and select (in the first cyclic group) the dimension "Supplier Name", I'm getting the result that for 860 leads I spent 1,600$ cost (the Correct result!)!!

and when I select (in the first cyclic group) the dimension "Day"  I'm getting the result that for 860 leads I spent 1,120$ cost

(wrong answer)!

Why the result changes when I display the data with another dimension?

The expression I use the calculate the Cost:

=sum(aggr(

if(Match(Leads_SupplierID,1,2,3,4),sum(CostAPI),

sum({<Leads_SupplierID={5}>}Cost * 2) //Google

+sum({<Leads_SupplierID-={5}>}Cost))

,$(=GetCurrentField([Cyclic1])),$(=GetCurrentField([Cyclic2]))))

I have included an example QVW

Thanks in advance

[sunny_talwar]

If that is not what you want... then why don't you list all your dimensions

=Sum(Aggr(

If(Match(Leads_SupplierID,1,2,3,4), Sum(CostAPI),

Sum({<Leads_SupplierID={5}>}Cost * 2) //Google

+Sum({<Leads_SupplierID-={5}>}Cost))

, Leads_SupplierName, Day, $(=GetCurrentField([Cyclic2]))))

You might have to do the same thing for your Cyclic2 cycle group

[ttal7878]

why do I have to list all the dimension?

If I use the "GetCurrentField" it shouldn't do the same thing?

[sunny_talwar]

Because you seem to be looking to aggregate on SupplierName and Daye even though the current dimension is showing Day... so if you want to do that... then you cannot really use GetCurrentField

[ttal7878]

I want to aggregate only by the display dimension, if the current dimension is "Day" so I would like to aggregate by Day and so on....

the amount of money that has been spent on day 1 is - 1600 $ and not 1120 $

In the original model, I have a lot of dimensions within the cyclin group, it would be impossible to list them all.....

[sunny_talwar · Accepted solution]

I guess this is because of your if statement... may be use set analysis...

=Sum(Aggr(Sum({<Leads_SupplierID = {1, 2, 3, 4}>} CostAPI) + Sum({<Leads_SupplierID = {5}>} Cost * 2) + Sum({<Leads_SupplierID -= {1, 2, 3, 4, 5}>} Cost), $(=GetCurrentField([Cyclic1])), $(=GetCurrentField([Cyclic2]))))

or just this

Sum({<Leads_SupplierID = {1, 2, 3, 4}>} CostAPI) + Sum({<Leads_SupplierID = {5}>} Cost * 2) + Sum({<Leads_SupplierID -= {1, 2, 3, 4, 5}>} Cost)

[ttal7878]

You are a genius! Thank you so much!

[ttal7878]

stalwar1‌ is it possible to display in the same Pivot table suppliers that have not enter any leads, (have 0 in column count leads) and we spent on them money

for example - supplier BB on day 2 , Count Leads=0 and CostAPI=400

and supplierid 4 on day 1 - Count Leads=0 and costapi=700

I would want to display in column "Leads" Zero  and  in column cost - the amount of money we spend

[sunny_talwar]

Based on the sample provided... what is the expected output you are hoping to see?

[ttal7878]

When you are on the dimension supplier name, two more rows will be added to the table, I have attached an image to the table I want