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