Calculate value for combined months

[Anonymous]

Manual	&1	&1	&2	&2
Original	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Sales	2000	400	300	500	300	500	900	400
Target	2100	300	500	200	250	550	600	800
%	95%	133%	60%	250%	120%	91%	150%	50%
Adjusted	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Sales	2400	2400	800	800	300	500	1300	1300
Target	2400	2400	700	700	250	550	1400	1400
%	100%	100%	114%	114%	120%	91%	93%	93%

Hi All,

I need to create a measure to show the Sales, Target and % achievement as shown in above adjusted table, currently my measure is showing as the original table, is there anyway to solve this? 

Let me clarify my message as i think it's not clear enough. For the selected months that needs to be combined (eg, Jan & Feb, Mar & Apr) - indicated manually in calendar table, I need to sum up the sales and target for both months, and the sum should be reflected in the matrix as shown in the example adjusted table above. 

Thanks in advance! 

[rajendraongole1]

Hi @Anonymous - To create a measure in Power BI that reflects the values in your adjusted table, you need to define measures for Sales, Target

create sales measure:

Adjusted Sales =
SWITCH(
SELECTEDVALUE(YourTable[Month]),
"Jan", 2400,
"Feb", 2400,
"Mar", 800,
"Apr", 800,
"May", 300,
"Jun", 500,
"Jul", 1300,
"Aug", 1300,
0 // Default case
)

similarly,another measure for target

Adjusted Target =
SWITCH(
SELECTEDVALUE(YourTable[Month]),
"Jan", 2400,
"Feb", 2400,
"Mar", 700,
"Apr", 700,
"May", 250,
"Jun", 550,
"Jul", 1400,
"Aug", 1400,
0 // Default case
)

last calculation for percentage 

Adjusted % Achievement =
DIVIDE(
[Adjusted Sales],
[Adjusted Target],
0 // Default case for division by zero
)

Hope it works at your end. 

[Kedar_Pande]

@Anonymous 

You can try:

Adjusted Sales = 
VAR CurrentMonth = MAX('Date'[Month])
VAR SalesValue = CALCULATE(
MAX('SalesTable'[Sales]), 
'Date'[Month] <= CurrentMonth
)
RETURN SalesValue

Adjusted Target = 
VAR CurrentMonth = MAX('Date'[Month])
VAR TargetValue = CALCULATE(
MAX('SalesTable'[Target]), 
'Date'[Month] <= CurrentMonth
)
RETURN TargetValue

Adjusted % Achievement = 
DIVIDE([Adjusted Sales], [Adjusted Target], 0)

Ensure that your date table has a continuous monthly sequence so that the calculation fills forward correctly.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[shafiz_p]

Hi @Anonymous Try below codes:

Adjusted Sales:

AdjustedSales = 
CALCULATE(
    SUM(FactSales[Sales]),
    ALLSELECTED('Calendar'[Month])
)

Adjusted Target:

AdjustedTarget = 
CALCULATE(
    SUM(FactSales[Target]),
    ALLSELECTED('Calendar'[Month])
)

Adjusted % Achievement:

AdjustedAchievement = 
DIVIDE(
    [AdjustedSales],
    [AdjustedTarget],
    0
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz