Strange result when calculating a ratio over YTD values

[moliveira-GA]

Hello guys, 

Please, I need your help to solve some strange result when I'm calculating a percentual ratio over two TotalYTD measures. Observe the value highlighted below:

As you can see, there is an absurd number as a result of ('Real YTD' / 'Plan YTD' - 1) calculation. In this situation, Plan YTD is zero but I've got this covered by using some 'IF' conditions. This is the measure for 'Var YTD %':

TotalVar% YTD = IF( AND( [TotRealYTD] = 0 , [TotPlanYTD] <> 0 ) ,

        -1 ,

            IF( AND( [TotRealYTD] > 0 , [TotPlanYTD] = 0 ) ,

            1 ,

                IF( AND( [TotRealYTD] < 0 , [TotPlanYTD] = 0 ) ,

                    -1,

                        IF( AND( [TotRealYTD] = 0 , [TotPlanYTD] = 0 ) ,

                        BLANK() ,

                            IF ( [TotPlanYTD] < 0 ,

                            (DIVIDE( [TotRealYTD] , [TotPlanYTD] ) -1) *-1 ,

                            DIVIDE( [TotRealYTD] , [TotPlanYTD] ) -1

                        )

                    )

                )

            )

        )

Those conditions are important to attend both Revenues and Expenses variation.

The measures for Real YTD and Plan YTD are:

TotRealYTD = TOTALYTD(

        SUM( bPrincipal_CR[Valor] ) ,

        fCalendar[Date] ,

        bPrincipal_CR[PlanReal] = "Realizado"

        )

TotPlanYTD = TOTALYTD( SUM(bPrincipal_CR[Valor]) ,

        fCalendar[Date] ,

        bPrincipal_CR[PlanReal] = "Planejado"

        )

Can you tell what am I doing wrong?

thanks in advance!

[moliveira-GA]

Hello @amitchandak 

thanks for your help! it really worked.

Allow me to say, it's a very clever alternative to the "IF", and I believe this solution will help me with other questions. Thanks again!

[amitchandak · Accepted solution]

@moliveira-GA , Please change order like

Switch( true(),

[TotRealYTD] = 0  &&  [TotPlanYTD] = 0, Blank(),

isblank([TotRealYTD])  && isblank([TotPlanYTD]) , blank() ,

isblank([TotRealYTD])  ||  isblank([TotPlanYTD]), 1,

[TotRealYTD] = 0  ||  [TotPlanYTD] = 0,1,

DIVIDE( [TotRealYTD] -[TotPlanYTD], [TotPlanYTD] ) 

)