RONCE Calculation

Question

Hello Power Bi community,&nbsp;&nbsp;Hope you are all doing great,&nbsp;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:&nbsp;As you will notice is depending on the row,&nbsp; the income is multiplied by:12 when is monthly based4 when is quarterly based1 on the year basis.&nbsp;(I am trying to create a measurement as the column E)&nbsp;any ideas on how to do this?&nbsp;&nbsp;ABCDEF1DateIncomeInventoryAssets ValueRONCEColumn E Formula (RONCE)2Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 600,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,650,000.0046%=(B2*12)/(C2+D2)3Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 225,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 615,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,572,500.0052%=(B3*12)/(C3+D3)4Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 215,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 620,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,495,000.0050%=(B4*12)/(C4+D4)5Q1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 640,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 620,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,495,000.0050%=(B5*4)/(C5+D5)6Apr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 208,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,417,500.0049%=(B6*12)/(C6+D6)7May&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 212,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 675,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,340,000.0051%=(B7*12)/(C7+D7)8Jun&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 235,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,262,500.0057%=(B8*12)/(C8+D8)9Q2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 655,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,262,500.0053%=(B9*4)/(C9+D9)10Jul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 245,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 663,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,185,000.0061%=(B10*110)/(C10+D10)11Aug&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 215,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 615,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,107,500.0055%=(B11*12)/(C11+D11)12Sep&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 260,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 625,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,030,000.0067%=(B12*12)/(C12+D12)13Q3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 720,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 625,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,030,000.0062%=(B13*4)/(C13+D13)14Oct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 228,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 685,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,952,500.0059%=(B14*114)/(C14+D14)15Nov&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 235,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,875,000.0062%=(B15*12)/(C15+D15)16Dec&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 248,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,500.0067%=(B16*12)/(C16+D16)17Q4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 711,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,500.0064%=(B17*4)/(C17+D17)182023 Total&nbsp;&nbsp; 2,726,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,500.0061%=(B18)/(C18+D18)

greg_deckler · Answer

randyvdr&nbsp;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 · Answer

Thank you&nbsp;Greg_Deckler&nbsp;&nbsp;just to not&nbsp; leave this post without the final result, the formula to calculate RONCE properly was&nbsp;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])&nbsp;Output:&nbsp;&nbsp;

randyvdr · Answer

DateIncomeInventoryAssets ValueRONCEColumn E Formula (RONCE)Jan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 600,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,650,00046%=(B2*12)/(C2+D2)Feb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 225,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 615,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,572,50052%=(B3*12)/(C3+D3)Mar&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 215,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 620,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,495,00050%=(B4*12)/(C4+D4)Q1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 640,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 620,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,495,00050%=(B5*4)/(C5+D5)Apr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 208,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,417,50049%=(B6*12)/(C6+D6)May&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 212,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 675,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,340,00051%=(B7*12)/(C7+D7)Jun&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 235,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,262,50057%=(B8*12)/(C8+D8)Q2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 655,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,262,50053%=(B9*4)/(C9+D9)Jul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 245,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 663,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,185,00061%=(B10*110)/(C10+D10)Aug&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 215,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 615,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,107,50055%=(B11*12)/(C11+D11)Sep&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 260,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 625,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,030,00067%=(B12*12)/(C12+D12)Q3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 720,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 625,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,030,00062%=(B13*4)/(C13+D13)Oct&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 228,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 685,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,952,50059%=(B14*114)/(C14+D14)Nov&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 235,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 665,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,875,00062%=(B15*12)/(C15+D15)Dec&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 248,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,50067%=(B16*12)/(C16+D16)Q4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 711,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,50064%=(B17*4)/(C17+D17)2023 Total&nbsp;&nbsp; 2,726,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 645,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,797,50061%=(B18)/(C18+D18)

greg_deckler · Answer

randyvdr&nbsp;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 · Answer

Hello Greg,&nbsp;Sorry for the miss explanation, in this case Date is not a text string.&nbsp;Where:Total Income = sum(Finance[Income])&nbsp;Net Inventory = VAR last_date=max('Finance'[Date])VAR lastNI= CALCULATE(&nbsp; &nbsp; SUM('Finance'[Inventory]),&nbsp; &nbsp; 'Finance'[Date]=last_date)ReturnlastNI&nbsp;Net Asset Value = VAR last_date = max('Finance'[Date])VAR LastNAV = CALCULATE(&nbsp; &nbsp; SUM('Finance'[Assets Value]),&nbsp; &nbsp; 'Finance'[Date]=last_date)ReturnLastNAV&nbsp;RONCE = // PLEASE&nbsp; YOUR HELP WITH THIS ONEDIVIDE([Total Income],[Net Inventory]+[Net Asset Value])&nbsp;&nbsp;The example data is:&nbsp;&nbsp;

Forum Discussion

RONCE Calculation

5 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters

	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)