Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mhgottsc
Helper I
Helper I

Create weighted revenue based off sales stage.

‎06-08-2022 07:37 AM

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!

 

mhgottsc_0-1654698951798.png

 

mhgottsc_1-1654698988345.png

 

 

Labels:
Message 1 of 12
745 Views
0
1 ACCEPTED SOLUTION
BeaBF
Memorable Member
Memorable Member
In response to mhgottsc

‎06-08-2022 09:00 AM

@mhgottsc 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]
 
BF
 

View solution in original post

Message 10 of 12
657 Views
0
11 REPLIES 11
BeaBF
Memorable Member
Memorable Member

‎06-08-2022 08:05 AM

@mhgottsc 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.

 

BF

Message 2 of 12
705 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:11 AM

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%

 

Message 3 of 12
693 Views
0
BeaBF
Memorable Member
Memorable Member
In response to mhgottsc

‎06-08-2022 08:14 AM

@mhgottsc  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,

BF

Message 4 of 12
689 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:20 AM

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

 

mhgottsc_1-1654701581474.png

 

 

Message 12 of 12
685 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:18 AM

Here you go! I had it filtered.

 

mhgottsc_0-1654701518795.png

 

Message 5 of 12
686 Views
0
BeaBF
Memorable Member
Memorable Member
In response to mhgottsc

‎06-08-2022 08:22 AM

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

 

BF

Message 6 of 12
681 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:47 AM

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

 

Message 9 of 12
664 Views
0
BeaBF
Memorable Member
Memorable Member
In response to mhgottsc

‎06-08-2022 09:00 AM

@mhgottsc 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]
 
BF
 
Message 10 of 12
658 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 09:06 AM

That seems to work! Thanks!

Message 11 of 12
648 Views
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:35 AM
Opportunity_RecIDDate_Became_Leadso_TypeCompany_RecIDStatusSales_StageExpected_Close_DateDate_ClosedClosed_FlagTotal_RevenueAgreement_Type
1265########Servers and Equipment19301Lost - Not a Good Fit6.0-Verbal################True$0.  
1352########Servers and Equipment19301Lost - Other6.0-Verbal################True$0.  
1420######## 19301Lost - Other1.0-Lead30-Jun-19########True$0.  
1427########Servers and Equipment19301Bundled In Proposal8.2-Not Won################True$0.  
1465########Servers and Equipment19301Lost – Too Expensive6.0-Verbal18-Jan-19########True$0.  
2888########Telephony19301Lost - Other8.2-Not Won################True$0.  
2939########Servers and Equipment19301Bundled In Proposal8.2-Not Won################True$0.  
1574######## 19302Lost - Other1.0-Lead################True$0.  
1479########Networking19305Lost – Too Expensive6.0-Verbal25-Jan-19########True$0.  
3055########Data Center19307Lost - Other8.2-Not Won################True$0.  
1647########Servers and Equipment19307Bundled In Proposal8.2-Not Won30-Jun-19########True$0.  
1648########Servers and Equipment19307Bundled In Proposal1.0-Lead################True$0.  
1650######## 19307Lost - Other1.0-Lead################True$0.  
1792########Servers and Equipment19307Lost - Other1.0-Lead31-Jul-19########True$0.  
1805########Telephony19307Lost - Other6.0-Verbal################True$0.  
2890########Consulting19307Lost - Other1.0-Lead################True$0.  
1474########Networking19339Won8.2-Not Won3-Apr-19########TRUE$0.  
1814########Internet Access19436Lost - Not a Good Fit6.0-Verbal################True$0.  
1206########Networking19460Lost - Not a Good Fit1.0-Lead################True$0.  
1210######## 19460Lost - Other1.0-Lead20-Jul-18########True$0.  
1309######## 19460Lost - Other8.2-Not Won30-Jun-19########True$0.  
1747########Consulting19460Lost - Other1.0-Lead################True$0.  
1669########Servers and Equipment19460Lost - No Decision6.0-Verbal################True$0.  
1839########Servers and Equipment19460Lost - Other1.0-Lead12-Jul-19########True$0.  
1275########Servers and Equipment19482Lost - Other8.2-Not Won################True$0.  
1288########Servers and Equipment19482Lost - No Decision6.0-Verbal30-Jun-19########True$0.  
Message 8 of 12
633 Views
0
mhgottsc
Helper I
Helper I
In response to BeaBF

‎06-08-2022 08:34 AM
Opportunity_RecIDDate_Became_Leadso_TypeCompany_RecIDStatusSales_StageExpected_Close_DateDate_ClosedClosed_FlagTotal_RevenueAgreement_Type
1265########Servers and Equipment19301Lost - Not a Good Fit6.0-Verbal################True$0.  
1352########Servers and Equipment19301Lost - Other6.0-Verbal################True$0.  
1420######## 19301Lost - Other1.0-Lead30-Jun-19########True$0.  
1427########Servers and Equipment19301Bundled In Proposal8.2-Not Won################True$0.  
1465########Servers and Equipment19301Lost – Too Expensive6.0-Verbal18-Jan-19########True$0.  
2888########Telephony19301Lost - Other8.2-Not Won################True$0.  
2939########Servers and Equipment19301Bundled In Proposal8.2-Not Won################True$0.  
1574######## 19302Lost - Other1.0-Lead################True$0.  
1479########Networking19305Lost – Too Expensive6.0-Verbal25-Jan-19########True$0.  
3055########Data Center19307Lost - Other8.2-Not Won################True$0.  
1647########Servers and Equipment19307Bundled In Proposal8.2-Not Won30-Jun-19########True$0.  
1648########Servers and Equipment19307Bundled In Proposal1.0-Lead################True$0.  
1650######## 19307Lost - Other1.0-Lead################True$0.  
1792########Servers and Equipment19307Lost - Other1.0-Lead31-Jul-19########True$0.  
1805########Telephony19307Lost - Other6.0-Verbal################True$0.  
2890########Consulting19307Lost - Other1.0-Lead################True$0.  
1474########Networking19339Won8.2-Not Won3-Apr-19########TRUE$0.  
1814########Internet Access19436Lost - Not a Good Fit6.0-Verbal################True$0.  
1206########Networking19460Lost - Not a Good Fit1.0-Lead################True$0.  
1210######## 19460Lost - Other1.0-Lead20-Jul-18########True$0.  
1309######## 19460Lost - Other8.2-Not Won30-Jun-19########True$0.  
1747########Consulting19460Lost - Other1.0-Lead################True$0.  
1669########Servers and Equipment19460Lost - No Decision6.0-Verbal################True$0.  
1839########Servers and Equipment19460Lost - Other1.0-Lead12-Jul-19########True$0.  
1275########Servers and Equipment19482Lost - Other8.2-Not Won################True$0.  
1288########Servers and Equipment19482Lost - No Decision6.0-Verbal30-Jun-19########True$0.  
Message 7 of 12
677 Views
0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

View All