cancel
Showing results for
Did you mean:
Helper I

## Create weighted revenue based off sales stage.

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!

1 ACCEPTED SOLUTION
Super User

@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

11 REPLIES 11
Super User

@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

Helper I

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%

Super User

@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

Helper I

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

Helper I

Here you go! I had it filtered.

Super User

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

BF

Helper I

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

Super User

@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

Helper I

That seems to work! Thanks!

Helper I
Helper I

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors