Show all information of a table in a pivot table

[takama13]

Hi,

(I've translated my business case in order to be a little bit clearer)

I have 3 tables regarding the usage of cars in cities :

- a data table, showing how cars have been used in cities

- a Cities information table, where it shows the required color and the minimum length allowed for a car

- a Cars information table, showing for different brands, the color and the length of several cars

The central table is the data table :

My target is to create a pivot table to show the cars that are allowed in a city, based on color and length.

I then created below pivot table :

Where I show the "minimum length - maximum length" existing for a car brand.

I used this expression :

min(TOTAL <[Car brand]> [Car length]) & ' - ' & max(TOTAL <[Car brand]> [Car length])

So with some color condition formatting, I will be able to see if a car brand is able to circulate in a city or not.

My issue is when I select a specific city. The pivot table does not work anymore.

If I select for example "NYC", I obtain :

What I want :

- to see only the city(ies) I selected

- to see all the car brands of my Cars table

- to see all the cars information 

I played with set analysis and aggr, but I failed to find the correct formula to do that.

Could you help me ?

PS: is it possible to attach a qvd file ?

[sunny_talwar]

Would you be able to share your qvw file to check this?

[takama13]

Hi Sunny,

Here it is.

[sunny_talwar · Accepted solution]

Try this expression

=If(Only(TOTAL <City> City) = Only({1}TOTAL <City> City), min({1} TOTAL <[Car brand]> [Car length]) & ' - ' & max({1} TOTAL <[Car brand]> [Car length]))

 

[takama13]

Thanks, working fine.

I'm still trying to understand what you are testing with

=If(Only(TOTAL <City> City) = Only({1}TOTAL <City> City)

But it's working

[sunny_talwar]

SelectedCity = list of city (notice {1} here)... not tested, but this might work also

=If(GetSelectedCount(City) = Only({1}TOTAL <City> City)