% of Target Help

[Tamking2]

I have been working on this for days, watched hours of videos, and still can't seem to get it right.

I am very new to Power BI but I assume this isn't as difficult as I am making it.

I have a historical Sales Table that shows what each employee did for Sales by Quarter in their first year and then annually. I want to measure their sales vs initial target and see how long it took them to reach their target. And then somehow show this as a visual by Company and then by Section. Ultimately I want the visual to show the trend toward how long it takes the average salesperson to reach their target and to ascertain if some sections reach it quicker than others.

I can't wrap my head around it and I'm hoping someone out there has had to do this before or can shed a little light on an approach.
Here is an example of the Data I have:

Table - Sales
Columns = Name, Year, Sales

Table - SalesPeople

Columns - Name, Department, Hire Date, Initial Target
I related Sales People to Total Sales by Name and created a Matrix Visual:

I used this formula to bring over the Target: 

Target =

VAR CurrentName = 'Sales'[Name]

Return

Maxx(Filter(All('SalesPeople'),SalesPeople[Name]=CurrentName), 'SalesPeople'[Initial Target])

Name	Dept	Target	1st Year	2nd Tear	3rd Year	4th Year	5th Year
Danielle	Candy	42500	15374	11529	31061	62074
Joe	Candy	500000	408	737543.3	1032561	1445585	2023819
John	OutDoor	100000	483281.9	389982.3	150234.4	232967.8	232969.8
Seth	Games	100000	164226	640504	2308262	2470775	223416
Alicia	Games	100000	234895	835773.7	1173788	1037398	546821
Jeff	Games	150000	64484.32	112740.9	33221.5	236755.9	69765.15
Rob	Toys	1000000	400511.5	1203007	704903.1	267336.3	1755161
Sue	Toys	100000	91788.05	302651.7	81193.8	284178.3	341014
Paul	Toys	500000	108653	532963.1	878045.6	1281198	1863347
Jane	Toys	100000	67096.3	830752.9	321825.5	427755.4	849920.3
Joshua	Toys	180000	986681.7	2104425	2179425	213877.7	404724
Melissa	Toys	400000	0	15353.5	420885	435885	67453.99

I think I should create a % column and use that as my value, but when I tried, it didn't exactly work...

% Target =

DIVIDE(Maxx('Sales','Sales'[Sales]),Maxx('Sales','Sales'[Target]), blank())

I may be completely off base, Any help would be appreciated

[Anonymous · Accepted solution]

Hi  @Tamking2 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

% Target = 
VAR _selname =
    SELECTEDVALUE ( 'Sales'[Name] )
VAR _target =
    CALCULATE (
        MAX ( 'SalesPeople'[Initial Target] ),
        FILTER ( 'SalesPeople', 'SalesPeople'[Name] = _selname )
    )
VAR _selyear =
    SELECTEDVALUE ( 'Sales'[Year] )
VAR _sales =
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Name] = _selname
                && 'Sales'[Year] <= _selyear
        )
    )
RETURN
    DIVIDE ( _sales, _target, BLANK () )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards