Forum Discussion
Calculating performance vs. target; 'left to get' versus target; performance growth in % terms
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 |
8 Replies
v-frfei-msftCommunity Support
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
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?
- Ashish_Mathur
Super User
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.
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
