Measure to check quarters and display different set of value

[evez]

I would like to create a measure "forecast" by checking the quarter selected and display different set of values. 

The result for each quarter is correct as shown in table below. however the total is blank. how do i go about get the total of Quarter 1 to 4. 

thanks.

Forecast =

SWITCH(

    true(),

SELECTEDVALUE(all_rates_v[fiscal_quarter])=1,[Actual],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=2,[Actual],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=3,[forecast],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=4,[budget]

)

result

Quarter	1	2	3	4	Total
	Forecast	Forecast	Forecast	Forecast	Forecast
	200	100	150	400

[v-sdhruv · Accepted solution]

Hi @evez ,

You can try this-

Forecast =
IF(
    HASONEVALUE(all_rates_v[fiscal_quarter]),
    SWITCH(
        TRUE(),
        VALUES(all_rates_v[fiscal_quarter]) = 1, [Actual],
        VALUES(all_rates_v[fiscal_quarter]) = 2, [Actual],
        VALUES(all_rates_v[fiscal_quarter]) = 3, [forecast],
        VALUES(all_rates_v[fiscal_quarter]) = 4, [budget]
    ),
    [Actual] + [Actual] + [forecast] + [budget]
)

 Here HASONEVALUE checks if only one quarter is selected.

Hope this helps!

[evez]

@v-sdhruv  thanks it works. but i change the code a bit to fit my need. 

Forecast =

VAR SelectedQtrs = CONCATENATEX(VALUES(all_rates_v[fiscal_quarter]),all_rates_v[fiscal_quarter],"")

RETURN

if(HASONEVALUE(all_rates_v[fiscal_quarter]),

SWITCH(

    true(),

SELECTEDVALUE(all_rates_v[fiscal_quarter])=1,[Actual],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=2,[Actual],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=3,[ForecastCQ],

SELECTEDVALUE(all_rates_v[fiscal_quarter])=4,[Budget]

),

if(find(1,SelectedQtrs,,0)>0,calculate([Actual],all_rates_v[fiscal_quarter]=1),"")+

if(find(2,SelectedQtrs,,0)>0,calculate([Actual],all_rates_v[fiscal_quarter]=2),"")+

if(find(3,SelectedQtrs,,0)>0,calculate([ForecastCQ],all_rates_v[fiscal_quarter]=3),"")+

if(find(4,SelectedQtrs,,0)>0,calculate([Budget],all_rates_v[fiscal_quarter]=4),"")

)

[FBergamaschi]

Forecast =
SUMX ( 
         VALUES ( all_rates_v[fiscal_quarter] ),
         IF ( 
                  all_rates_v[fiscal_quarter] = 1, [Actual],

                  IF (
                          all_rates_v[fiscal_quarter])=2,[Actual],
                          IF (                      

                                    all_rates_v[fiscal_quarter]=3,[forecast],
                            IF ( 
                                      all_rates_v[fiscal_quarter])=4,[budget]
                                )
                            )

                      )

           )
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[evez]

@FBergamaschi  thanks for your reply. but it seems not working. now the quarterly number is incorrect too. while the total is still blank. any other way to work around?