PBI is grouping by NAME and not by ID

[Anonymous]

Hi guys,

I have this situation in PBI, I hope you can help.

I have my table visual to make sure everything looks like in the data source.
Right now, I have selected only the product "Chocolate con Almendras" (Almond chocolate ice cream).

As you can see in the table, there are 5 "Chocolate con almendras" products but this is because
the 5 of them are different products which come in different presentations (presentacion), and
have unique unit prices (PU Novo Foods) and production costs (Costo U Baccio). Therefore, in the data source,
I have given a unique ID for each of them, however, when I want to separately visualize the unit costs(PU NOVO FOODS) against the production costs (COSTO U BACCIO) (which is the dark blue line) of each of them, Power BI is grouping the product by name and not by a unique ID, and the result is that PBI is summing up all the unit prices/production costs and showing me that there's only 1 product with that name even though they have unique IDs. Here's the image:

So, how I can sort them by ID?

Thanks all.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

You need to create a unique product field to use as your chart axis.

The easiest way to do this is in Power Query, select your [Producto] column, then Ctrl + click your [Presentacion] column so they are both highlighted, then on the ribbon go to the Add Column tab, then select Merge. This will create a new column with unique product descriptions to be used in your visuals.

Pete

[Anonymous]

Thank you for your answer.

So, I will be creating a new column with no repeated names, right?

I have merged my PRODUCT column with the ID_presentation column, as the PRESENTATION value is stored in another query (cause I'm just trying to relate IDs). 

 Like in the following image:

So, now I have the thing that the new column added an extra number to my products (as highlighted in yellow in the image above). If I remove them, would I be getting the same situation as the previous one?

Thanks in advance.

[BA_Pete]

@Anonymous 

If you use your [PRODUCTO BUENO] field as your chart axis you will show separate totals for each type of Chocolate Ice Cream etc.

And yes, if you remove the [ID_Presentacion] numbers from this field, your chart will group all your Chocolate Ice Cream types into a single total again.

My recommendation for best presentation would be to merge your main table with the table that holds the presentation descriptions on [ID_presentacion], then do the column merge on [Producto] and [Presentacion]. This will give you items on your axis like:

Choc ice cream 250ml

Choc ice cream 125ml etc.

rather than:

Choc ice cream 34

Choc ice cream 17 etc. which is what you have now.

Hope this makes sense.

Pete

[mahoneypat]

Have you tried also adding your ID_Producto column to the x-axis of your visual?  Once you do that, you can expand to next level of the hierarchy in upper right of the visual to show both the group and ID info.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat