Forum Discussion
RONCE Calculation
Hello Power Bi community, Hope you are all doing great, I am trying to create a financial KPI in my power Bi model, and I tought the easiest way to explain it is with the following Excel tab...
randyvdr Are you using the Auto Time Intelligence hierarchy or do you have extra columns for Year, Quarter, Month? Basically, you need to use ISINSCOPE or HASONEVALUE to make the determination of the multiplier in either case. I would highly recommend NOT using the auto time intelligence hierarchy and instead using your own Year, Quarter and Month columns for that as the auto time intelligence uses a hidden table that you can't reference.
Thank you Greg_Deckler
just to not leave this post without the final result, the formula to calculate RONCE properly was
RONCE =
VAR _mult= SWITCH(TRUE(),HASONEVALUE('Calendar'[Month]),12,HASONEVALUE('Calendar'[Quarter]),4,HASONEVALUE('Calendar'[Year]),1)
return
DIVIDE([Total Income]*_mult,[Net Inventory]+[Net Asset Value])
Output: