topic Re: Strange result when calculating a ratio over YTD values in DAX Commands and Tips

Strange result when calculating a ratio over YTD values

moliveira-GA — Thu, 06 Apr 2023 19:55:00 GMT

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!

Re: Strange result when calculating a ratio over YTD values

amitchandak — Fri, 07 Apr 2023 04:08:51 GMT

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] )

)

Re: Strange result when calculating a ratio over YTD values

moliveira-GA — Mon, 10 Apr 2023 17:38:48 GMT

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!