May 17 2021 05:33 AM
May 17 2021 05:33 AM
Bonjour, je souhaite créer un tableau croisé dynamique à partir de 2 tables excel situées dans le même classeur excel (1 table avec le nom et la date de naissance, 1 table avec le nom et l'adresse)
j'ai créé une relation entre les 2 tables (la clé commune est le nom de la personne) mais quand je crée mon tcd, il me renvoie tous les enregistrements (le nom, la date de naissance et toutes les adresses.
j'imagine que j'ai zappé une étape mais laquelle?
merci par avance de votre aide.
May 23 2021 01:55 PM
What is a tcd? Please show us the steps in how you create the tcd.
May 25 2021 04:24 AM
a TCD is tableau croisé dynamique in french...pivot table in english
I've got 2 sheets:
- sheet MM with all products created for my organisation with details such as weight, hierachy, category...,
- Sheet supplier with same products and name of supplier (one product has only one supplier)
I have created a relation beetween those 2 sheets through power pivot. the article is the common value.
then I need to create a pivot table, with material reference, description, hierarchy, ...and name of supplier.
but the results are wrong as each material is displayed to be linked to all suppliers
I guess that when creating the relation, I need to get a one to one relation. but I can't change this parameter (it is always multiple to one).
can you help me?
May 25 2021 11:15 AM - edited May 25 2021 12:37 PM
What does the expected result look like? What should the pivot result look like?
In other words what is the parameter for the query (beggar?).
example: I am looking for product x to find out which supplier is providing product x
or I want to see all products that the unit of measure is KG, or M2, or P, or PC
so what is the beg?
May 26 2021 03:26 AM
The result should show all products with details like hierarchy from sheet MM + name of supplier from sheet supplier (for example records in red below are correct....supplier of product 53252325 is aeroglass only, supplier from product 53252684 is IFT FILTERS only)
May 26 2021 02:09 PM - edited May 26 2021 02:11 PM
May 27 2021 02:09 PM - edited May 27 2021 02:37 PM
There was no issue. I just checked the relationship, cleared your pivot table fields then selected nom1 first in the vendor table, then selected all the fields in MM.
Apparently in the TCD/Pivot Table, first field you select dictates what other related field will display. for example in your original TCD you selected the Articles field first, so the TCD displayed all Vendors related to the Article field. Now that you know how TCD behaves, you can now be mindful of what you are looking for. So if you wanted a list of MM related to the Vendors, then you have to choose a field in the vendors table first before it will list the related information about the first field selection. In your original TCD you selected Articles then the pivot table gave you a list of Vendors related to the Articles, however if you selected the Vendor nom1 first, then the TCD will display the records related to nom1 in the vendor table from the MM table.
the key is i want a list of things from this list (a list within a list), so if you're shopping for apples and want to know who sells these apples, then you would select the apple article so you can see the list of vendors selling that apple, however in your case you wanted a list of products for each vendor so you have a vendor list and the TCD will list within each vendor what products they have available.
To step this tutorial up a notch, you can then use Slicers or Measures.
You can use slicers to narrow your list down to specifics. So when you click an article slicers for example, you only want to see a list of vendors having to do with a specific article so the Slicer will filter through the list to display only the vendors that have a specific Paint, or other desired product. With measures they are associated with calculated columns for example if your MM table had amounts of number of price per kilogram then you would create a calculated column, then click on measures if you want to see an article that is associated with the price per kilogram calculated column.
I hope I explained it to you clearly.