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
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) |
@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:
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
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) |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
14 | |
9 |