Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Table level calculation

Hi, I am stuck with achieving table level calculation, any quick help is appreciated. it could be a measure or column    Problem is to pick the appropriate cell value and subtract it against the pr...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Anonymous ,

     

    I don't see a Fleet No in your example, but the following calculated column should work for the data you posted:

    myResult = 
VAR vCurrentValue = Facttable[Value]
VAR vCurrentIndex = Facttable[Index]
RETURN
    IF(
        vCurrentIndex = 1,
        0,
        vCurrentValue - CALCULATE(SUM(Facttable[Value]), Facttable[Index] = vCurrentIndex - 1 , ALLEXCEPT(Facttable, Facttable[ID]))
    )

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
Anonymous's avatar
Anonymous
Not applicable
5 years ago
ID EndWeekValueIndexExpected Results Calculation  
11501    10/7/201824236310 Default "0"If Index=1 then 0 
11501   11/4/201824871126348 "=C3-C2"248711 - 242363Index1-Index2
11501    12/2/201825316334452 "=C4-C3"253163 - 248711Index2-Index3
11501    12/31/201825886945706 "=C5-C4"  
11501    1/27/201926455955690 "=C6-C5"  
11501    2/24/201927015165592 "=C7-C6"  
23488    3/24/201927426910 Default "0"  
23488    4/21/201927991625647 "=C9-C8"  
23488    5/19/201928385933943 "=C10-C9"  
23488    6/16/201929055546696 "=C11-C10"  
23488    7/14/201929474254187 "=C12-C11"  
Anonymous's avatar
Anonymous
Not applicable
5 years ago
ID EndWeek Value IndexExpected Results Calculation  
11501 10/7/2018 242363 10 Default "0"If Index=1 then 0 
11501 11/4/2018 248711 26348 "=C3-C2"248711 - 242363Index1-Index2
11501 12/2/2018 253163 34452 "=C4-C3"253163 - 248711Index2-Index3
11501 12/31/2018 258869 45706 "=C5-C4"  
11501 1/27/2019 264559 55690 "=C6-C5"  
11501 2/24/2019 270151 65592 "=C7-C6"  
23488 3/24/2019 274269 10 Default "0"  
23488 4/21/2019 279916 25647 "=C9-C8"  
23488 5/19/2019 283859 33943 "=C10-C9"  
23488 6/16/2019 290555 46696 "=C11-C10"  
23488 7/14/2019 294742 54187 "=C12-C11"