Pivot Table expressions

[AB108]

Hi,

I am working on a complex dashboard and facing a problem.

Attaching sample data and a sample qvw.

I am joining the two tables in table boxes.

I am then creating a Pivot Table.

I want that when I filter on a particular Dim2 value, it gets filtered in the Pivot table with the same results.

Because Dim2 is also part of the Pivot table expressions, if I filter on Dim2. I get this:

[chriscammers]

Good Afternoon,

I want to be as kind as possible but this example has a number of problems.

//Your Expression...
(sum(total{<Dim3={'B1'}>}Met2))*(sum({<Dim2>}Met1)/sum(total Met1))

The first part uses the total Qualifier so basically you get the result for Met2 where Dim3 = B1 across EVERY Row because that would be the total.

The second part IGNORES the selection on Dim2 and divides by the TOTAL of Met1

I appreciate your effort to simulate your problem but in this case please try to explain the business case and more accurately reflect your real data. the way I see it Dim3 and Dim2 have a cartesian relationship and then you are messing with the selections and calculation context.

Please consider describing the problem you are trying and asking specifically about it. Many community members have been around for years and we've worked many industries, we can probably help.

Best of luck 

[AB108]

Hi,

Thanks for the reply.

So if I simplify the business need for my problem, it is that %contribution of Dim2(Customer) towards Met1(Sales) drives the distribution of Met3(Costs) in various categories(Dim3).

There are a few more business cases but they are similar to this one.

I am able to create variables for the %contribution and actually able to achieve the distribution but the numbers change if I end up filtering the data for a particular customer.