Calculating performance vs. target; 'left to get' versus target; performance growth in % terms

[Anonymous]

I am a new power BI user and just coming to grips with the setup. I have a large flat file loaded from excel with performance by sales rep across multiple years, multiple quarters. at a deal / transaction level.

I have loaded in a separate table with the targets by sales rep, by quarter.

I'd now like to create 3 calculations:

- Sales rep performance (sum of USD performance) versus target expressed as a percentage, for the selected QTR

- Sales rep 'left to get' versus target (i.e. target minus sum of performance) for selected QTR, but left to get cannot be less than zero i.e. if performance is greater than the target, then left to get should be zero

- Sales rep performance growth / decline in % terms versus another selected QTR

I think this is going to involve DAX but I'm not sure where to start.

Very grateful for some help.

Tables below are Data. Then Targets. The picture is the relationships.

Opportunity Name	Service Offering	USD Value	Region	Stage	Forecast	FY&Q	Sales Rep
Randomtext - 3724384903	Offering 5	4723.23	South	Won	Closed	FY13 Q2	John Doe 1
Randomtext - 2761410753	Offering 3	0	UK	Prospected	Pipeline	FY19 Q1	John Doe 2
Randomtext - 2271928993	Offering 5	48613	Nordics	Qualified Out	Closed	FY12 Q2	John Doe 3
Randomtext - 1942677260	Offering 2	54.11	Nordics	Won	Closed	FY18 Q4	John Doe 4
Randomtext - 5101192472	Offering 5	34642.03	Nordics	Qualified Out	Closed	FY13 Q4	John Doe 5
Randomtext - 7975257163	Offering 6	0	South	Qualified Out	Closed	FY19 Q1	John Doe 6
Randomtext - 5482864152	Offering 5	371.67	South	Won	Closed	FY12 Q4	John Doe 6
Randomtext - 3828510690	Offering 1	537.12	Nordics	InLegal	Commit	FY19 Q1	John Doe 7
Randomtext - 1195647477	Offering 5	32992.41	Nordics	Qualified Out	Closed	FY13 Q4	John Doe 8
Randomtext - 8467719711	Offering 3	0	West	Qualified Out	Closed	FY18 Q4	John Doe 9
Randomtext - 7577179194	Offering 5	3150	Midwest	Won	Closed	FY13 Q4	John Doe 10

Sales Rep	FY18 Q1	FY18 Q2	FY18 Q3	FY18 Q4	FY19 Q1
John Doe 1	21000	56000	95000	91000	42000
John Doe 2	14000	95000	22000	86000	15000
John Doe 3	63000	78000	67000	93000	74000
John Doe 4	48000	32000	62000	44000	50000
John Doe 5	87000	55000	65000	15000	68000
John Doe 6	26000	85000	72000	10000	8000
John Doe 7	88000	99000	79000	58000	45000
John Doe 8	38000	15000	39000	52000	76000
John Doe 9	91000	84000	14000	76000	71000
John Doe 10	50000	61000	29000	94000	78000

[v-frfei-msft]

Hi @Anonymous ,

One sample for your reference. Please check the following steps as below.

1. Unpivot the Targets table, please refer to the M code in power query as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZG9DoMwDIRfBTEzxEn8k71TXwExVupU3n9rrFxUqKcThz/77Oz7+jzfn+VxvhZatzVTSqkry9DGUPg1ux7bBcvdpnovz3mooQ1xwEq3pYzfakNF0Wb6NWDVQ6C8YIpAK1L4sD+MPQy6M0IKX8P1bwuY+C7YwVCmmEYJfqDUXWRsDRSU4dd4EfOV7J6pTAxDVQLWLs9juMB8D0V2pYDR71J9dfAZ49rkx3LHFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, #"FY18 Q1" = _t, #"FY18 Q2" = _t, #"FY18 Q3" = _t, #"FY18 Q4" = _t, #"FY19 Q1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Rep", type text}, {"FY18 Q1", Int64.Type}, {"FY18 Q2", Int64.Type}, {"FY18 Q3", Int64.Type}, {"FY18 Q4", Int64.Type}, {"FY19 Q1", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sales Rep"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

2. We need create three calculated tables as below.

Sales Rep = DISTINCT(UNION(VALUES(Data[Sales Rep]),VALUES('Targets'[Sales Rep])))

FY = DISTINCT(UNION(VALUES(Data[FY&Q]),VALUES(Targets[Attribute])))

another selected QTR = VALUES(FY[FY&Q])

3. Then create relationship between tables as the pic.

4.Afther that we can create three measures to get the results as we need.

Sales rep performance = 
CALCULATE ( SUM ( Data[USD Value] ) ) / CALCULATE ( SUM ( Targets[Value] ) )

left to get = 
VAR _data =
    CALCULATE ( SUM ( Data[USD Value] ) )
VAR _tar =
    CALCULATE ( SUM ( Targets[Value] ) )
VAR _min = _tar - _data
RETURN
    IF ( _min > 0, _min / _tar, 0 )

Sales rep performance growth = 
VAR _sele =
    CALCULATE (
        SUM ( Data[USD Value] ),
        FILTER ( ALL ( Data ), 'Data'[FY&Q] = SELECTEDVALUE ( FY[FY&Q] ) ),
        VALUES ( Data[Sales Rep] )
    )
VAR sele2 =
    CALCULATE (
        SUM ( Targets[Value] ),
        FILTER ( ALL ( Targets ), 'Targets'[Attribute] = SELECTEDVALUE ( FY[FY&Q] ) ),
        VALUES ( Targets[Sales Rep] )
    )
RETURN
    DIVIDE ( [Sales rep performance] - _sele / sele2, _sele / sele2 )

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

@v-frfei-msftHi. I am working with the dummy file / your pbix, and moving info back in to my working model as I work through the components and understand them.

When I bring in the 'Left to Get' calculated measure (which you helped me to create) to a table on my visualizations panel, it has the strange effect of calculating left to get for roles / reps that have been filtered out using page level filters. This forces the table to dramatically expand and include people / roles that were filtered out prior to adding in the calculated measure.

I can see that the page level filters have been applied to the visual level filters, but the unwanted info still shows in the table.

Is there any way to prevent this from happening?

[v-frfei-msft]

Hi @Anonymous ,

Did you create correct relationships between tables? And did you add the same columns as my pbix to the visual?

Regards,

Frank

[Anonymous]

Thanks @v-frfei-msft - I am still working this through and following the logic.

Why did you need to create the 2 calculated tables of FY and Sales Rep? These fields already exist in 'data'. Why can't we just create a relationship between the data table and the targets table?

[v-frfei-msft]

Hi @Anonymous ,

If we create a relationship between the data table and the targets table and create slicers for the columns you want to filter. The table will be filtered and you will not get the correct result in the measure as we create.

Regards,

Frank

[Ashish_Mathur]

Hi,

After unpivoting the target table, there will be duplicates appearing in the Sales rep column.  For a relationship to flow from the Sales rep column of the Data Table to the Sales rep column of the Target table, the Sales rep column of the Target table should only have unique values.  So that is the primary reason.  Furthermore, whenever, there is a Date column, there should always be  Calendar Table so that you can use Date/Time Intelligence functions.  Both the target table and the Data Table should be linked to the Calendar Table.

[Anonymous]

Thanks @Ashish_Mathur -

So any time I want to use a field in power BI that has duplicate values in the underlying data, I would need to create the calculated table? That seems incredibly onerous. I am new to PowerBi but up until the time that I posted this question, I (appear to!) have managed fine without having to do this.

Would appreciate some further detail on when it is appropriate / required to do this, and when it is not.

[Ashish_Mathur]

Hi,

In the Table on the 1 side of a Many to one relationship, the column that you are drawing a relationship to should have unique values.