Sum based on text name and percentage uplift (sales)

[Anonymous]

Hi Experts

See my measure 2 below. I am trying to find sum all values in order type by 5% and all other values like managed Service by 7% and Prof Services by 8% and since ill have mulitple ocurances of MS and PS i have add MAX to my measuere. But i cannot get the measure to return a result back. 

Order Type	Sales	Result		Uplift
Managed Service	1	1.07		0.07
Professional Service	2	2.16		0.08
New	3	3.15		0.05
old	4	4.2		0.05
Other	5	5.25		0.05
Not only MS and PS have an uplift of 7 and 8 (%)
Everthing else 5%

Measure 1

Sales Forecast = 

VAR SALESLY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-1,YEAR))

VAR SALES2LY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-2,YEAR))

Var Factor = 1.05

Return

DIVIDE(SALESLY+SALES2LY,2,0)*Factor

This work if i want an uplift of 5% across all Order type's (no issues)

Measure 2 = 

Sumx( Values(FACT_NEWBI_NewSalesDashboard[OrderType]) ,

[Sales Forecast]*Switch(TRUE(),

max(FACT_NEWBI_NewSalesDashboard[OrderType]) = "Managed Services" , 1.07,

max(FACT_NEWBI_NewSalesDashboard[OrderType]) = "Professional Services" , 1.08,

1.05) )

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Result =
SUMX(
    FILTER(ALL('Table'),'Table'[Order Type]=EARLIER('Table'[Order Type])),[Sales] + [Uplift]* [Sales])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , you should multiple measure 1 , if you plan to do same in measure 2

Sales Forecast =
VAR SALESLY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-1,YEAR))
VAR SALES2LY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-2,YEAR))
Var Factor = 1.05
Return

DIVIDE(SALESLY+SALES2LY,2,0)

[Anonymous]

Hi Amit - do not follow - you last comment.....sorry help - i am mulitpling by measure 1 - [Sales Forecast] is measure 1  - i just made a error when posting the question