RONCE Calculation

[randyvdr]

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 table:

As you will notice is depending on the row,  the income is multiplied by:

12 when is monthly based

4 when is quarterly based

1 on the year basis.

(I am trying to create a measurement as the column E)

any ideas on how to do this?

	A	B	C	D	E	F
1	Date	Income	Inventory	Assets Value	RONCE	Column E Formula (RONCE)
2	Jan	200,000.00	600,000.00	4,650,000.00	46%	=(B2*12)/(C2+D2)
3	Feb	225,000.00	615,000.00	4,572,500.00	52%	=(B3*12)/(C3+D3)
4	Mar	215,000.00	620,000.00	4,495,000.00	50%	=(B4*12)/(C4+D4)
5	Q1	640,000.00	620,000.00	4,495,000.00	50%	=(B5*4)/(C5+D5)
6	Apr	208,000.00	645,000.00	4,417,500.00	49%	=(B6*12)/(C6+D6)
7	May	212,000.00	675,000.00	4,340,000.00	51%	=(B7*12)/(C7+D7)
8	Jun	235,000.00	665,000.00	4,262,500.00	57%	=(B8*12)/(C8+D8)
9	Q2	655,000.00	665,000.00	4,262,500.00	53%	=(B9*4)/(C9+D9)
10	Jul	245,000.00	663,000.00	4,185,000.00	61%	=(B10*110)/(C10+D10)
11	Aug	215,000.00	615,000.00	4,107,500.00	55%	=(B11*12)/(C11+D11)
12	Sep	260,000.00	625,000.00	4,030,000.00	67%	=(B12*12)/(C12+D12)
13	Q3	720,000.00	625,000.00	4,030,000.00	62%	=(B13*4)/(C13+D13)
14	Oct	228,000.00	685,000.00	3,952,500.00	59%	=(B14*114)/(C14+D14)
15	Nov	235,000.00	665,000.00	3,875,000.00	62%	=(B15*12)/(C15+D15)
16	Dec	248,000.00	645,000.00	3,797,500.00	67%	=(B16*12)/(C16+D16)
17	Q4	711,000.00	645,000.00	3,797,500.00	64%	=(B17*4)/(C17+D17)
18	2023 Total	2,726,000.00	645,000.00	3,797,500.00	61%	=(B18)/(C18+D18)

[Greg_Deckler]

@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

[randyvdr]

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:

[Greg_Deckler]

@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.

[randyvdr]

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:

[randyvdr]

Date	Income	Inventory	Assets Value	RONCE	Column E Formula (RONCE)
Jan	200,000.00	600,000.00	4,650,000	46%	=(B2*12)/(C2+D2)
Feb	225,000.00	615,000.00	4,572,500	52%	=(B3*12)/(C3+D3)
Mar	215,000.00	620,000.00	4,495,000	50%	=(B4*12)/(C4+D4)
Q1	640,000.00	620,000.00	4,495,000	50%	=(B5*4)/(C5+D5)
Apr	208,000.00	645,000.00	4,417,500	49%	=(B6*12)/(C6+D6)
May	212,000.00	675,000.00	4,340,000	51%	=(B7*12)/(C7+D7)
Jun	235,000.00	665,000.00	4,262,500	57%	=(B8*12)/(C8+D8)
Q2	655,000.00	665,000.00	4,262,500	53%	=(B9*4)/(C9+D9)
Jul	245,000.00	663,000.00	4,185,000	61%	=(B10*110)/(C10+D10)
Aug	215,000.00	615,000.00	4,107,500	55%	=(B11*12)/(C11+D11)
Sep	260,000.00	625,000.00	4,030,000	67%	=(B12*12)/(C12+D12)
Q3	720,000.00	625,000.00	4,030,000	62%	=(B13*4)/(C13+D13)
Oct	228,000.00	685,000.00	3,952,500	59%	=(B14*114)/(C14+D14)
Nov	235,000.00	665,000.00	3,875,000	62%	=(B15*12)/(C15+D15)
Dec	248,000.00	645,000.00	3,797,500	67%	=(B16*12)/(C16+D16)
Q4	711,000.00	645,000.00	3,797,500	64%	=(B17*4)/(C17+D17)
2023 Total	2,726,000.00	645,000.00	3,797,500	61%	=(B18)/(C18+D18)