Forum Discussion
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
What that I am trying to achieve is this.
- Obtain the no of months as of today the sales person is with the company (
- Match the number of months they are with us to the expected sales month.
- 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.
- Otherwise if it is more than X but less than Y it will return Y expected sales.
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/38490Regards,
Lin
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
