Comparing budget and sales table with working filters

[Rallerbabz]

Hello! 🙂

I am looking to set up a graph that compares a budget with the actual number of sales.

I have a table with the name of each salesmen with their monthly budget throughout 2019 on table 1.

On table 2 I have the ID of the specific sale and who were responsible with various columns that I would like to filter for

Table 1
Month            Name    Budget
01-01-2019    John       20
01-01-2019    Frank      25
01-02-2019    John       35
01-02-2019    Frank      15

etc

Table 2
SaleID          Name    Category    Region
5676NSD5   John      Online        A
45SD6775    Frank     Online        B
586NSD56   John      InStore       A
49SD6775    Frank     InStore       B
506NSD56   John      Online        A
41SD6775    Frank     InStore       B

etc

So in this case, i would like to graph to look like:

Where the red line will be the budget(And going up/down according to the monthly budget), and each bar will contain the number of total sales in that month, with the posibility of filtering for Name, Category, Region etc so that both the budget and the total sales adjust accordingly.

I think my main issue is that, I can't seem to set up the proper relationships between the tables. I have quite few tables; one with all the sales, the budget, categories, name of each salesman etc. I have taken a picture of my relationships and changed the text to english so it makes more sense to you, pardon my paint skills:

[v-yulgu-msft]

Hi @Rallerbabz,

Is there a date column in Table2? If not, it's hard to establish a relationship between two tables. For example, as you placed Date column onto X-axis, how should we determine the record "SaleID 5676NSD5" in Table2 is calculated in which month, Jan or Feb?

Best regards,

Yuliana Gu

[AlB]

Hi @Rallerbabz

By "Number of total sales" you mean just a distinctcount of SalesID?? I.e., for John in Table2 "Number of total Sales" would be 3 because it appears 3 times?

[Rallerbabz]

Hi @AlB - Yes exactly, a distinctcount of SalesID. Hi @v-yulgu-msft - Yes, sorry! There is of course a date column in table 2, that we can use to identify which month the sale belongs to.