link columns

[Thomas897]

Hello everyone,

I have an issue. I explain myself with an example.

Ex: I sell burgers for the same price but some client do not want pickles for example.

Let's say that I sold 100 burgers for january : that is 1 column.

And I have also 1 column for each ingredients that I use (pickels, bun, cheese, steak, etc.).

How do I link these ingredients to my burgers sale knowing that I want my 100 burgers to appear but also I want to show that I only used 90 pickles, 100 steaks, 80 cheese and 100 buns on the same graph.

Thank you !

[Anonymous]

Hi @Thomas897

There are different ways to go about your topic. For example you could have a certain table structure, which allows you to count how many ingredients you've used for each burger. The table structure could look something like this:

Burger	Bun	pickle	Steak	Cheese
1	1	0	1	1
2	1	1	1	0
3	1	1	1	1
4	1	0	1	0
5	1	1	1	1

Here I've used binary columns to determine what ingredients where used for each burger.

Does this make sense? I'll gladly elaborate.

Best,

Martin

[Thomas897]

Hi @Anonymous,

I would like to show that when I click on my supplier (ex: Kraft for my cheese), how much of their product is used on my 100 burgers.

I have all these informations but I cannot find a way to connect it...

Best,

Thomas

[Anonymous]

@Thomas897

Can you show me a sample of how your data is stored?

[Thomas897]

@Anonymous

Company

Accountable date

Supplier

Kind of cost

cost in $

Month

Year

Country

But my amount of cheese, bun, steaks used is in another table....

[Anonymous]

@Thomas897

Is there a way to connect the tables?

[Thomas897]

@Anonymous

Yes, it works but some of the numbers are not right because for example, for my 100 burgers of july, we used cheese that was in stock for june and we did not need to buy more so Power BI understands that we did not need cheese at all in july... I mean it is really hard to explain and I cannot really show you (I do not think my boss would appreciate it).

[Anonymous]

@Thomas897

I understand. I really want to help, but I kind of need to see how the data is stored, so my advice would be to create a table with false data and then give it to me so I can see if it is even possible, and then if it is, how we can go about solving it. It's going to be really hard for me without. Dummy data is fine, I just need to see the table structure if that makes sense.

[Thomas897]

@Anonymous

Well, I will comeback to you later because I do not have all the data right now...

Thank you for your time !

[Thomas897]

Hi @Anonymous,

Month	Year	Company	Country	Burgers sold	Buns	Steaks	Cheese	Pickle
1	2016	X	FRA	5920	5920	5920	5328	4144
2	2016	X	FRA	6343	6343	6343	5709	4440
3	2016	X	FRA	2439	2439	2439	2195	1707
4	2016	X	FRA	6534	6534	6534	5881	4574
5	2016	X	FRA	7762	7762	7762	6986	5433
6	2016	X	FRA	2346	2346	2346	2111	1642

So basically, I want to show that for 5920 burgers sold in 01/2016, 5236 and 4144 cheese have been used.

The thing is that I have a pie chart (link between 2 tables) with my suppliers and, as we do not receive bill from them every month, Power BI believes that any cheese or pickles have been used. So I'd like to know if it is possible.

Thanks 🙂

[Anonymous · Accepted solution]

@Thomas897

Is that how your current data is stored? What can you tell me about the related table? What does the desired result look like?

[Thomas897]

I have 2 tables, the one that I sent you and another one where each bill is written by supplier. 
The link between both tables are the month, the year, and country.

The problem is : I have a pie chart where pickles, bun, steak, cheese are stored and a graph where we can see how many burgers we have sold by month.

So, for example, when I click on pickles for january, I can see that I have sold 100 burgers but in reality, I only need 80 pickles so I want to show that I only used 80 pickles.

[Anonymous]

@Thomas897

Alright I understand your data structured now. I also understand your pie chart and burger graph. See the picture below for what I have so far.

Now I'm trying to understand where you click on pickles. In the table or on the pie chart? I'm still trying to figure out, where you want the 80 pickles to be displayed?

[Thomas897]

Hi @Anonymous,

I am sorry if it is not clear but I am going to try to explain myself in details.

I have 2 tables : one with my burgers sold (by month in one hand, in the other hand how many pickles I used) and one with all data about suppliers (date of purchase, how much, what country made the purchase, kind of purchase, etc.).

I created a relation between my 2 tables with '&' between countries and date.

I have a pie chart where my kind of cost are stored (pickels, cheese, etc.) and I have a histogram where I can see how many burgers I sold by month.

When I click on pickels, I want to see how many I used for real. Now, I am only able to see, that when I have a bill with the date of purchase, that 100 on a 100 burgers sold have pickles on. In fact that is not true, so I want to use my 'pickles I used' column but it does not work because it creates another column on my graph as I only want to see how many burgers I sold.

I tried to make myself as clear as possible, I hope you are going to understand what is my concern.

I am sorry but I cannot send screenshot.

Thanks