Multipication between a column with unique values and a measure

[AlexisBiancotti]

Hello,

I have a problem, maybe someone can help me.

I am making a projection of the production of the plant. To accomplish this i have all the sales in the last three years, and i have sapareted them in their correspondent month. There are three tables, one per year, each one has the code of the product and the quantity that has been sold.

I created a measure call "Promedio_Mensual", this makes the average per month from the time interval that is selected from the filters. The filters are Year, Product and Month, the messeuere responds correctly. Then i have this table, that it has the code of the product, the name of the product, the month, and "Proyeccion" a number that represents the variation from the average in the month itself.

I need to multiply this average per month "Promedio_Mensual" from each product with the corresponding values from the "Proyeccion" column, this comes from a excel.

Thanks in advance 

[Cmcmahan]

So I would first ask about your data.  You have a column of variation multipliers for projections and a month in which they apply, but how do you relate a multiplier to a year?  What indicates to PowerBI that they're related?  A composite key of IdArticulo and Mes?

Can you show us an image of your relationships for the affected tables?

I'm also curious about your data.  Is there a particular reason you separated it into a table for each year instead of just having a year column?  It seems setting up measures and filters would be easier if it was all in one table

[AlexisBiancotti]

I have a table for each year, wich relations are made in the next way.

The principal reason beacuse there is a table for each year, is that it come from a excel, that is downloaded from our ERP. The only file that ussualy changes is this year and is the only one that i modify. I am new in this program and it was the way that a figure it out, how to make the relantionships and show the data in this grafic making a line from each year.

Each line is a year and the values are the sales. My objective is to add the values from the projection in this grafic, and filter it with the date filters that i have. I dont need to relate a multiplier with the year only with the month, beacuase  the meseaure "Promedio_Ventas" is related with the tables from each year.

[Cmcmahan]

Ah, then that makes this easy. PowerBI can easily combine tables, and it will re-combine every time you reload the data.

The first thing you need to decide to do is whether you want to append this data as rows or as columns.  I would suggest rows for a beginner, since it should be easier.

First, go into the Edit Queries, and for each year, add a column called "Sales_Year" or similar.  Then when it asks for a custom formula you just set it equal to the year for that table.

Once you've done this for all 3 of your tables, on the top ribbon navigate to Home > Append Queries on the far right. Select "Append Queries as New" and select your tables.  This should create a new table (called Append1, but I would rename it) with all your data in one place. Close & Apply changes.

Now doing things like slicing should be much easier with this table, and once you update your reports you can even hide the old tables by right clicking the table and selecting hide so you don't accidentally use old data.

At this point, you should be able to set up the relationship between your Mes/Ano/Lists tables and the new large table. Also remove the old relationships to the individual tables to avoid later confusion. From here, you should be able to create a new measure on your large table similar to below:

PROJECTED_VALUE = CALCULATE( Datos_All[Promedio_Ventas] * RELATED(Proy[Proyeccion]) )

Change names of tables/measures as required.

[AlexisBiancotti]

Thanks for your answer, i made what you say and its much easier to make the calculations!

But i have another problem, when i want to write the measure that you have told me:

Proyeccion = calculate([Promedio_ventas]*related(Proy[Proyeccion]))

Promedio_Ventas: is the average per month from sales (measure)

Proy[Proyeccion]: Is the column that has the values to multiply

Happens the next thing:

I erased all the other tables, and now the relationship is the following:

Do yo know how to solve this?

Thanks in advance, you help me a lot!

[Cmcmahan · Accepted solution]

I can take a crack at it. 

Let's see.  The first issue I see is how you have your data set up now.  It is MUCH simpler than it used to be, but I think you've combined tables a little bit too much.  The problem Power BI is having is that you've told it that IDs in the Proy table match up with every identical ID in the Comb_X_Clients table. However there are multiple of the same ID in both tables.  Power BI assumes you know what you're doing at this point, and it sort of gives up on many:many relationships, since they can get very complicated.

The way to fix this is to set up one or more 1:many or 1:1 relationships.  Looking at your data, it seems you have a number of fields that could be brought into their own table.  First, it looks like you have an ID, Product name, and possibly more.  Take any data used to describe one product, and move that to it's own table.  Here's a basic example from a fake store:

Now that you've got each type of item described in a standalone table, you can now update your sales figures by month table to just include an item ID.  Any data that is used to describe every copy of the product except the ID should be out of the sales table at this point.  

Immediately once this 1:many relationship is created, I can make a new calculated column on my Sales table.  

And so we can easily create a table or graph from this information:

For more information on this topic, you can Google Normalization of Data. There are plenty of resources out there, and even with the data above we could pull the date out into it's own dimension table that only holds dates. I'm not sure it's completely necessary in your case, but the option is there.

Once you have your data in a 1:many relationship, you should be able to work with it as you see fit. Good luck out there @AlexisBiancotti