topic Re: Create weighted revenue based off sales stage. in DAX Commands and Tips

Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 14:37:32 GMT

Hi,

I need to create a weighted revenue based on sales stage.

For example, the total revenue for sales stage 2 needs to be weighted by .10 (10% of total revenue)

The total revenue for sales stage 3 needs to be weighted by .25 (25% of total revenue)

And so on.

Attached are pictures of the vis and model.

Thanks!

Re: Create weighted revenue based off sales stage.

BeaBF — Wed, 08 Jun 2022 15:05:35 GMT

Anonymous hi! I suggest you to create a calculated column in which, using if conditions, you go to assign a weight to each range, then create a measure to weigh the revenue.

If you tell me the weights I will create the code for each column and measure.

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:11:35 GMT

Thank you for the quick response, the wights are as follows:

Sales stage 0.0 = 0%

1.0 = 0%

2.0 = 10%

3.0 = 25%

4.0 = 50%

5.0 = 75%

6.0 = 90%

7.0 = 100%

Re: Create weighted revenue based off sales stage.

BeaBF — Wed, 08 Jun 2022 15:14:01 GMT

Anonymous in the picture, there are only 6 rows with 6 values for the field Sales_Stage. Can you paste the real data on which create the measures?

Thx,

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:18:45 GMT

Here you go! I had it filtered.

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:20:15 GMT

With no filters applied but I only care about 0, 1, 2, 3, 4, 5, 6, 7,

Re: Create weighted revenue based off sales stage.

BeaBF — Wed, 08 Jun 2022 15:22:18 GMT

Anonymous can you paste me data instead of picture? so i can fix a correct measure based on your data.

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:34:24 GMT

Opportunity_RecID	Date_Became_Lead	so_Type	Company_RecID	Status	Sales_Stage	Expected_Close_Date	Date_Closed	Closed_Flag	Total_Revenue	Agreement_Type
1265	########	Servers and Equipment	19301	Lost - Not a Good Fit	6.0-Verbal	########	########	True	$0.
1352	########	Servers and Equipment	19301	Lost - Other	6.0-Verbal	########	########	True	$0.
1420	########		19301	Lost - Other	1.0-Lead	30-Jun-19	########	True	$0.
1427	########	Servers and Equipment	19301	Bundled In Proposal	8.2-Not Won	########	########	True	$0.
1465	########	Servers and Equipment	19301	Lost – Too Expensive	6.0-Verbal	18-Jan-19	########	True	$0.
2888	########	Telephony	19301	Lost - Other	8.2-Not Won	########	########	True	$0.
2939	########	Servers and Equipment	19301	Bundled In Proposal	8.2-Not Won	########	########	True	$0.
1574	########		19302	Lost - Other	1.0-Lead	########	########	True	$0.
1479	########	Networking	19305	Lost – Too Expensive	6.0-Verbal	25-Jan-19	########	True	$0.
3055	########	Data Center	19307	Lost - Other	8.2-Not Won	########	########	True	$0.
1647	########	Servers and Equipment	19307	Bundled In Proposal	8.2-Not Won	30-Jun-19	########	True	$0.
1648	########	Servers and Equipment	19307	Bundled In Proposal	1.0-Lead	########	########	True	$0.
1650	########		19307	Lost - Other	1.0-Lead	########	########	True	$0.
1792	########	Servers and Equipment	19307	Lost - Other	1.0-Lead	31-Jul-19	########	True	$0.
1805	########	Telephony	19307	Lost - Other	6.0-Verbal	########	########	True	$0.
2890	########	Consulting	19307	Lost - Other	1.0-Lead	########	########	True	$0.
1474	########	Networking	19339	Won	8.2-Not Won	3-Apr-19	########	TRUE	$0.
1814	########	Internet Access	19436	Lost - Not a Good Fit	6.0-Verbal	########	########	True	$0.
1206	########	Networking	19460	Lost - Not a Good Fit	1.0-Lead	########	########	True	$0.
1210	########		19460	Lost - Other	1.0-Lead	20-Jul-18	########	True	$0.
1309	########		19460	Lost - Other	8.2-Not Won	30-Jun-19	########	True	$0.
1747	########	Consulting	19460	Lost - Other	1.0-Lead	########	########	True	$0.
1669	########	Servers and Equipment	19460	Lost - No Decision	6.0-Verbal	########	########	True	$0.
1839	########	Servers and Equipment	19460	Lost - Other	1.0-Lead	12-Jul-19	########	True	$0.
1275	########	Servers and Equipment	19482	Lost - Other	8.2-Not Won	########	########	True	$0.
1288	########	Servers and Equipment	19482	Lost - No Decision	6.0-Verbal	30-Jun-19	########	True	$0.

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:35:06 GMT

Opportunity_RecID	Date_Became_Lead	so_Type	Company_RecID	Status	Sales_Stage	Expected_Close_Date	Date_Closed	Closed_Flag	Total_Revenue	Agreement_Type
1265	########	Servers and Equipment	19301	Lost - Not a Good Fit	6.0-Verbal	########	########	True	$0.
1352	########	Servers and Equipment	19301	Lost - Other	6.0-Verbal	########	########	True	$0.
1420	########		19301	Lost - Other	1.0-Lead	30-Jun-19	########	True	$0.
1427	########	Servers and Equipment	19301	Bundled In Proposal	8.2-Not Won	########	########	True	$0.
1465	########	Servers and Equipment	19301	Lost – Too Expensive	6.0-Verbal	18-Jan-19	########	True	$0.
2888	########	Telephony	19301	Lost - Other	8.2-Not Won	########	########	True	$0.
2939	########	Servers and Equipment	19301	Bundled In Proposal	8.2-Not Won	########	########	True	$0.
1574	########		19302	Lost - Other	1.0-Lead	########	########	True	$0.
1479	########	Networking	19305	Lost – Too Expensive	6.0-Verbal	25-Jan-19	########	True	$0.
3055	########	Data Center	19307	Lost - Other	8.2-Not Won	########	########	True	$0.
1647	########	Servers and Equipment	19307	Bundled In Proposal	8.2-Not Won	30-Jun-19	########	True	$0.
1648	########	Servers and Equipment	19307	Bundled In Proposal	1.0-Lead	########	########	True	$0.
1650	########		19307	Lost - Other	1.0-Lead	########	########	True	$0.
1792	########	Servers and Equipment	19307	Lost - Other	1.0-Lead	31-Jul-19	########	True	$0.
1805	########	Telephony	19307	Lost - Other	6.0-Verbal	########	########	True	$0.
2890	########	Consulting	19307	Lost - Other	1.0-Lead	########	########	True	$0.
1474	########	Networking	19339	Won	8.2-Not Won	3-Apr-19	########	TRUE	$0.
1814	########	Internet Access	19436	Lost - Not a Good Fit	6.0-Verbal	########	########	True	$0.
1206	########	Networking	19460	Lost - Not a Good Fit	1.0-Lead	########	########	True	$0.
1210	########		19460	Lost - Other	1.0-Lead	20-Jul-18	########	True	$0.
1309	########		19460	Lost - Other	8.2-Not Won	30-Jun-19	########	True	$0.
1747	########	Consulting	19460	Lost - Other	1.0-Lead	########	########	True	$0.
1669	########	Servers and Equipment	19460	Lost - No Decision	6.0-Verbal	########	########	True	$0.
1839	########	Servers and Equipment	19460	Lost - Other	1.0-Lead	12-Jul-19	########	True	$0.
1275	########	Servers and Equipment	19482	Lost - Other	8.2-Not Won	########	########	True	$0.
1288	########	Servers and Equipment	19482	Lost - No Decision	6.0-Verbal	30-Jun-19	########	True	$0.

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 15:47:58 GMT

If you need more of the data please let me know!

Re: Create weighted revenue based off sales stage.

BeaBF — Wed, 08 Jun 2022 16:00:18 GMT

Anonymous So, you can create a calculated column like this:

Weight = SWITCH(

LEFT(Tabella[Sales_Stage], 1),

"0", 0,

"1", 0.10,

"2", 0.20,

"3", 0.25,

"4", 0.50,

"5", 0.75,

"6", 0.9,

"7", 1)

Then another calculated column like this:

Revenue_weight = Tabella[Weight] * Tabella[Total_Revenue]

Re: Create weighted revenue based off sales stage.

Anonymous — Wed, 08 Jun 2022 16:06:08 GMT

That seems to work! Thanks!