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 previous cell value

I tried few dax but no luck

total = CALCULATE(SUM('Facttable (2)'[Value]),ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No]))
total - cellvalue = 
CALCULATE(SUM('Facttable (2)'[Value]),ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No])) - CALCULATE(SUM('Facttable (2)'[Value]),FILTER(ALLEXCEPT('Facttable (2)','Facttable (2)'[Fleet No]),'Facttable (2)'[Index]-1))
IDEndWeekValueIndexExpected Results Calculation  
1150110/7/201824236310 Default "0"If Index=1 then 0 
1150111/4/201824871126348 "=C3-C2"248711 - 242363Index1-Index2
1150112/2/201825316334452 "=C4-C3"253163 - 248711Index2-Index3
1150112/31/201825886945706 "=C5-C4"  
115011/27/201926455955690 "=C6-C5"  
115012/24/201927015165592 "=C7-C6"  
234883/24/201927426910 Default "0"  
234884/21/201927991625647 "=C9-C8"  
234885/19/201928385933943 "=C10-C9"  
234886/16/201929055546696 "=C11-C10"  
234887/14/201929474254187 "=C12-C11"  
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
     

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    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

      hi selimovd thanks and appreciate your quick response

       

      I did try VAR on index but was not aware that VAR could be used for values also.

       

      Thanks again!

      Best wishes

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey Anonymous ,

         

        I'm happy it works 😊

        Yes, VAR is pretty dynamic, you can save the result of measures, the current column value or a row like done here and also whole tables, for example with the SUMMARIZE or VALUES function.

         

        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"