Forum Discussion

Vinayak1991's avatar
Vinayak1991
Regular Visitor
2 years ago
Solved

Replacing subtotal values with another formula

I have month wise data of certain expenses. I have plotted this data in Matrix as expenses in rows and months in columns. I have already activated columns subtotal from visual format which is giving me total value of expense for all the months, but I need only difference of last two months in subtotal column or I need one additional column at the end of all months, showing difference of last two months  I have attached images for both currently I am having and image for what I need, Does it possible in DAX or another function??Data currently availableFormat I need

Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    Vinayak1991 

    You can follow this pattern: 

    LastTwoMonhsdiff = 
Var __relation = SUMMARIZE(ALL(financials), financials[Month Name], financials[Month Number]) 
Var __maxMonthNum = MAXX(__relation, financials[Month Number]) 
Var __lastToLastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum - 1), CALCULATE(SUM(financials[ Sales])))
Var __lastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum ) , CALCULATE(SUM(financials[ Sales]))) 
RETURN IF(HASONEVALUE(financials[Month Name]), CALCULATE(SUM(financials[ Sales])), __lastMonth - __lastToLastMonth  )



     

    I think the new visual calculations feature will make this pattern quiet simple. 


    If the post helps please give a thumbs up


    If it solves your issue, please accept it as the solution to help the other members find it more quickly.


    Tharun

     

1 Reply

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    Vinayak1991 

    You can follow this pattern: 

    LastTwoMonhsdiff = 
Var __relation = SUMMARIZE(ALL(financials), financials[Month Name], financials[Month Number]) 
Var __maxMonthNum = MAXX(__relation, financials[Month Number]) 
Var __lastToLastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum - 1), CALCULATE(SUM(financials[ Sales])))
Var __lastMonth = SUMX(FILTER( __relation, financials[Month Number] = __maxMonthNum ) , CALCULATE(SUM(financials[ Sales]))) 
RETURN IF(HASONEVALUE(financials[Month Name]), CALCULATE(SUM(financials[ Sales])), __lastMonth - __lastToLastMonth  )



     

    I think the new visual calculations feature will make this pattern quiet simple. 


    If the post helps please give a thumbs up


    If it solves your issue, please accept it as the solution to help the other members find it more quickly.


    Tharun