cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

elseif

Hi all,
I have two columns in my table.

first is "Curr Yr Received $" and second is "prior Yr Received $".

I need  create third column which will be count variance% ( (curr - prior) / prior).

but in this situation i should have 4 numbers (100%, -100%, 0% , someNumber%)

 

When "curr Yr" is high 0 and "prior yr" is 0,

I have some problem with "100%", how can I change my formula to replace or change "0%" with "100%"

 

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: elseif

@Vrto95 you never put that as requiement first, it is not wrong, it is working as expected, since you mentioned it, we can imrove the expression

 

Variance % =
IF( Table[CurrYr] = 0 && Table[PrevYr] = 0, 0, 
DIVIDE( Table[CurrYr] - Table[PrevYr], Table[PrevYr], 1 )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Highlighted
Super User VII
Super User VII

Re: elseif

@Vrto95 try this, change table name and column name as per your model

 

Variance % =
DIVIDE( Table[CurrYr] - Table[PrevYr], Table[PrevYr], 1 )





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: elseif

@parry2k I tried it but when  "curr yr" and "prior yr" equals zero my "variance % " need to be equals zero too.

In your solution it equals 100% and it's wrong.

Highlighted
Super User VII
Super User VII

Re: elseif

@Vrto95 you never put that as requiement first, it is not wrong, it is working as expected, since you mentioned it, we can imrove the expression

 

Variance % =
IF( Table[CurrYr] = 0 && Table[PrevYr] = 0, 0, 
DIVIDE( Table[CurrYr] - Table[PrevYr], Table[PrevYr], 1 )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper I
Helper I

Re: elseif

@parry2k  Thanks 🙂 ... it worked.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors