Populate a column using visual calculations and a lookup

[Troekoe]

Yo!
After creating a visual and using all types of visual calculations I am now in the process of making a dynamic forecast for my visual.

I am using a parameter to set a value and based on the selected value of this parameter I lookup a value in an earlier column.

This value should populate a new column for me in order to continue.

Consider this table:

Month	SUMCUM	Forecast	Forecast_month
jan	1275
feb	2334
mrt	3044
apr	3700
mei	1757	1757
jun	4460
jul	3965
aug	3418
sep	4600
okt	3517
nov	1121
dec	4705

The DAX used in the third column (Forecast) is as follows:
Forecast = LOOKUPVALUE([SUMCUM], [Month], [selectedvalue_tekst])

In this formula [selectedvalue_tekst] is the measure used for the paramter (SELECTEDVALUE())

However after using multiple methods such as 

 = IF(ISBLANK(Forecast), MAX(Forecast), MAX(Forecast))

I am unable to populate the Forecast_month column with the value in the Forecast column.

This is the desired result using visual calculations:

Month	SUMCUM	Forecast	Forecast_month
jan	1275		146,417
feb	2334		146,417
mrt	3044		146,417
apr	3700		146,417
mei	1757	1757	146,417
jun	4460		146,417
jul	3965		146,417
aug	3418		146,417
sep	4600		146,417
okt	3517		146,417
nov	1121		146,417
dec	4705		146,417

NOTE: 1757 / 12 = 146,417
I need this to be dynamic, so the number 1757 can be changed.
Thanks,

KR

--Troekoe

[johnbasha33 · Accepted solution]

@Troekoe 
Hi, 1st use this
Forecast_Value =
VAR SelectedValue = SELECTEDVALUE('Table'[selectedvalue_tekst])
RETURN
IF(ISBLANK(SelectedValue), BLANK(),
DIVIDE(MAX('Table'[Forecast]), 12)
)

and then

Forecast_month =
VAR ForecastValue = [Forecast_Value]
RETURN
IF(ISBLANK('Table'[Forecast]), ForecastValue, ForecastValue)


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @johnbasha33  provided, and i want to offer some more information for user to refer to.

hello @Troekoe , based on your descrition, you can refer to the following solution.

Sample data is the same as you provided.

1.Create a calculated column in table.

Month_num = MONTH(CONVERT([Month]&" "&1,DATETIME))

2.Create a paramater table

3.Create the following measures

Forecast = MAXX(FILTER(ALLSELECTED('Table'),[Month_num]=[Parameter Value]),[SUMCUM])

Display_for = IF(SUM('Table'[SUMCUM])=[Forecast],[Forecast]) 

For_Month = DIVIDE([Forecast],12)

Output

The measure changes dynamically.

Best Regards!

Yolo Zhu

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

[Troekoe]

Thanks you for the more in depth reply!

This helps me understand it a lot more.

[johnbasha33 · Accepted solution]

@Troekoe 
Hi, 1st use this
Forecast_Value =
VAR SelectedValue = SELECTEDVALUE('Table'[selectedvalue_tekst])
RETURN
IF(ISBLANK(SelectedValue), BLANK(),
DIVIDE(MAX('Table'[Forecast]), 12)
)

and then

Forecast_month =
VAR ForecastValue = [Forecast_Value]
RETURN
IF(ISBLANK('Table'[Forecast]), ForecastValue, ForecastValue)


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[Troekoe]

Yo! Thanks for the reply. I tested this and it seems to work. However after analyzing my problem further I have decided to go with another solution. As to not go further in depth I used RUNNINGSUM() to populate further columns with the lookupvalue as it generates the same number for rows/months in the future as 100 cumulative is 100 in the next month since the next month is empty.