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 Try:
Column =
VAR __Multiplier =
SWITCH( TRUE(),
LEFT([Date],1) = "Q", 4,
CONTAINSSTRING([Date], "Total"), 1,
12
)
VAR __Result = DIVIDE([Income] * __Multiplier, [Inventory] + [Value])
RETURN
__Result
Hello Greg,
Sorry for the miss explanation, in this case Date is not a text string.
Where:
Total Income = sum(Finance[Income])
Net Inventory =
VAR last_date=max('Finance'[Date])
VAR lastNI= CALCULATE(
SUM('Finance'[Inventory]),
'Finance'[Date]=last_date)
Return
lastNI
Net Asset Value =
VAR last_date = max('Finance'[Date])
VAR LastNAV = CALCULATE(
SUM('Finance'[Assets Value]),
'Finance'[Date]=last_date)
Return
LastNAV
RONCE =
// PLEASE YOUR HELP WITH THIS ONE
DIVIDE([Total Income],[Net Inventory]+[Net Asset Value])
The example data is:
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)returnDIVIDE([Total Income]*_mult,[Net Inventory]+[Net Asset Value])Output: