Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
randyvdr
Frequent Visitor

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 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?

 

 ABCDEF
1DateIncomeInventoryAssets ValueRONCEColumn E Formula (RONCE)
2Jan       200,000.00       600,000.00         4,650,000.0046%=(B2*12)/(C2+D2)
3Feb       225,000.00       615,000.00         4,572,500.0052%=(B3*12)/(C3+D3)
4Mar       215,000.00       620,000.00         4,495,000.0050%=(B4*12)/(C4+D4)
5Q1       640,000.00       620,000.00         4,495,000.0050%=(B5*4)/(C5+D5)
6Apr       208,000.00       645,000.00         4,417,500.0049%=(B6*12)/(C6+D6)
7May       212,000.00       675,000.00         4,340,000.0051%=(B7*12)/(C7+D7)
8Jun       235,000.00       665,000.00         4,262,500.0057%=(B8*12)/(C8+D8)
9Q2       655,000.00       665,000.00         4,262,500.0053%=(B9*4)/(C9+D9)
10Jul       245,000.00       663,000.00         4,185,000.0061%=(B10*110)/(C10+D10)
11Aug       215,000.00       615,000.00         4,107,500.0055%=(B11*12)/(C11+D11)
12Sep       260,000.00       625,000.00         4,030,000.0067%=(B12*12)/(C12+D12)
13Q3       720,000.00       625,000.00         4,030,000.0062%=(B13*4)/(C13+D13)
14Oct       228,000.00       685,000.00         3,952,500.0059%=(B14*114)/(C14+D14)
15Nov       235,000.00       665,000.00         3,875,000.0062%=(B15*12)/(C15+D15)
16Dec       248,000.00       645,000.00         3,797,500.0067%=(B16*12)/(C16+D16)
17Q4       711,000.00       645,000.00         3,797,500.0064%=(B17*4)/(C17+D17)
182023 Total   2,726,000.00       645,000.00         3,797,500.0061%=(B18)/(C18+D18)
5 REPLIES 5
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hello Greg,

 

Sorry for the miss explanation, in this case Date is not a text string.

 

randyvdr_0-1682964456775.png

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_1-1682964569756.png

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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_0-1682971505135.png

 

randyvdr
Frequent Visitor

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors