cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
naoki Regular Visitor
Regular Visitor

Creating performance gauge based on a separate table

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. 

5 REPLIES 5
fhill Senior Member
Senior Member

Re: Creating performance gauge based on a separate table

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...

Community Support Team
Community Support Team

Re: Creating performance gauge based on a separate table

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
naoki Regular Visitor
Regular Visitor

Re: Creating performance gauge based on a separate table

@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.

 

 

 Capture.JPG

Community Support Team
Community Support Team

Re: Creating performance gauge based on a separate table

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fhill Senior Member
Senior Member

Re: Creating performance gauge based on a separate table

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 NameDate JoinedCurrent Month SalesMonths Since HireLookup Expect Sales
Will Johnson1/3/2019 0:00150001012002
Roger Sherman3/5/2019 0:00800089000
George Read3/8/2019 0:00500078001
Gunning Bedford5/15/2019 0:00320052000
John Dickinson8/1/2019 0:0012003800
Richard Bassett8/31/2019 0:009002300
Jocob Bromm10/8/2019 0:0025001
Daniel Jenifer11/1/2019 0:00001

 

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

MonthsExpected Sales
01
1100
2300
3800
41200
52000
65000
78001
89000
910000
1012002
1112050
1212150
1312250
1412600
1512800
1613050
1713550
1815000

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)