Forum Discussion

Troekoe's avatar
Troekoe
Frequent Visitor
2 years ago
Solved

Populate a column using visual calculations and a lookup

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:

MonthSUMCUMForecastForecast_month
jan1275  
feb2334  
mrt3044  
apr3700  
mei17571757 
jun4460  
jul3965  
aug3418  
sep4600  
okt3517  
nov1121  
dec4705  

 

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:

MonthSUMCUMForecastForecast_month
jan1275 146,417
feb2334 146,417
mrt3044 146,417
apr3700 146,417
mei17571757146,417
jun4460 146,417
jul3965 146,417
aug3418 146,417
sep4600 146,417
okt3517 146,417
nov1121 146,417
dec4705 146,417

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


KR

--Troekoe

 

Need Help
  • johnbasha33's avatar
    johnbasha33
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • johnbasha33's avatar
    johnbasha33
    Super User
    2 years ago

    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's avatar
      Troekoe
      Frequent Visitor
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Troekoe
      Frequent Visitor
      2 years ago

      Thanks you for the more in depth reply!

      This helps me understand it a lot more.