Creating performance gauge based on a separate table

[naoki]

Hi all, 

I have 2 tables, 

First table consist of the sales person gross sales ($)

Second table holds the data on what is their expected gross sales, and constitution to define their performance within a given range. 

How can I link this 2 tables up to gauge the sales person performance. 

Currently, I have input the figures in DAX, however, the performance gauge might change from time to time thus I require it to be in another table. 

[v-lili6-msft]

hi @naoki 

You need to create a relationship between two tables.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

If you still have the problem, please share some simple sample data and your expected output.

Regards,

Lin

[naoki]

@v-lili6-msft @fhill

What that I am trying to achieve is this. 

1. Obtain the no of months as of today the sales person is with the company (
2.  Match the number of months they are with us to the expected sales month. 
3. In expected sales table if the number of months the sales person is with us equal or lesser than X months than it will return X month expected sales.
4. Otherwise if it is more than X but less than Y it will return Y expected sales.

 

[v-lili6-msft]

hi, @naoki 

Sample data and expected output would help tremendously.
Please share some simple sample data.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[fhill]

I hope this gets you started....  Again, we are having to make allot of assumptions based on not seeing the raw data?

*** Assuming you want to include Days of the month in the Months since hired calculation???

Months Since Hire = IF ( DAY(TODAY()) >= DAY('Sales Roster'[Date Joined]), DATEDIFF('Sales Roster'[Date Joined],TODAY(),MONTH) , DATEDIFF('Sales Roster'[Date Joined],TODAY(),MONTH) - 1)

*** Lookup their Expected Sales based on the above Months calculation.

Lookup Expect Sales = LOOKUPVALUE('Expect Sales'[Expected Sales], 'Expect Sales'[Months], 'Sales Roster'[Months Since Hire])

*** Sounds like you want another Column yet with some IF Logic to determine the final result, but i'm not sure if you want a Month to Month comparison, or a Total Sales since hire???

To help you further, please read the 'How to Get Your Question Answered Quickly' blog, and Post Samples of your Data (cleanse sensitive names, dates, and figures if needed).  Or create a fake data set and post the full Power BI File for further help.

Thank You,

Forrest

*** Assuming this is what your table looks like? ** The first 3 columns are in the raw table, and the next two Custom Columns come from the above code. ***

Sales Person Name	Date Joined	Current Month Sales	Months Since Hire	Lookup Expect Sales
Will Johnson	1/3/2019 0:00	15000	10	12002
Roger Sherman	3/5/2019 0:00	8000	8	9000
George Read	3/8/2019 0:00	5000	7	8001
Gunning Bedford	5/15/2019 0:00	3200	5	2000
John Dickinson	8/1/2019 0:00	1200	3	800
Richard Bassett	8/31/2019 0:00	900	2	300
Jocob Bromm	10/8/2019 0:00	250	0	1
Daniel Jenifer	11/1/2019 0:00	0	0	1

Again, assuming this is what your Table looks like???

Months	Expected Sales
0	1
1	100
2	300
3	800
4	1200
5	2000
6	5000
7	8001
8	9000
9	10000
10	12002
11	12050
12	12150
13	12250
14	12600
15	12800
16	13050
17	13550
18	15000

[fhill]

Please post samples from each table, and cleanse the data if needed to not post sensitive figures.  Do the tables have an 'Employee Key' or other value that uniquely identifies each employee?   It would also help if you mock up what you are trying to make the final visual look like...