Forum Discussion

ChloeDL's avatar
ChloeDL
Frequent Visitor
4 years ago
Solved

Formula Help!!

Basically, we have this huge dataset with individual line items for every transaction which includes prj info, budget, fiscal year etc. First, we need a formula to get the cumulative spent by budget ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  , ChloeDL 

     

    Is this result you want to achieve?

    you just need to create two new columns:

     

     

    sum = CALCULATE(SUM('Table'[actuals]),FILTER(ALL('Table'),'Table'[actuals]<=EARLIER('Table'[actuals])))

     

     

    value = 'Table'[TBbudget]-'Table'[sum]

     

    This is my testing PBIX  file.

     

    Best regards,

    Community Support Team Selina zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

daXtreme's avatar
daXtreme
Solution Sage
4 years ago

Hi ChloeDL 

 

It's good you show what you want to accomplish, we appreciate that... but where the piece where you show your data and model?

ChloeDL's avatar
ChloeDL
Frequent Visitor
4 years ago

Thanks for your response. Unfortunately, I work for GCC and cannot give you a screnshot of the data but I'm hoping this helps. Here's the formula we currently use for the cumulative spend. Which works well but doesn't seperate by "bucket of money".  We're using the matrix visuals to show each projects within each budget "bucket" and fiscal years either as columns or as a slicer. 

Cumulative spent = CALCULATE(SUM(Append1[Actual Expenditures]),ALL('Append1'),Append1[FiscalYear] <= MAX('Append1'[FiscalYear]))
 
Here's a screnshot of the relationships made, all of our data is within the Append1 table. 
 

 

Here's a copy of our data columns, highlighted are the "used" columns for this requirement. 

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    Hi there.

     

    ChloeDL wrote:

    Basically, we have this huge dataset with individual line items for every transaction which includes prj info, budget, fiscal year etc. First, we need a formula to get the cumulative spent by budget by year. Second we need a formula that will subtract the actuals from given total budget amount again by budget and fiscal. Thing is the dataset doesn’t have budget amounts.

    I don't quite get this. In one sentence you say there's budget in the table, in another you say you don't have the budget amounts... Not sure if I can help here 😞

     

     

     

    • ChloeDL's avatar
      ChloeDL
      Frequent Visitor
      4 years ago

      Sorry for not being clear. The budget colum shows the bucket from which each actuals is coming out of (b2018 or b2021). I've tested things and added the TBBudget column which shows the total budget amount but I need it to subtract each actuals 

       

      Currently it shows 

       

      BudgetActualsTBBudget
      B2018

      $10

      		$100
      B2018$20$100
      B2018$30$100
      B2021$40$200
      B2021$50$200

       

      Where we need it to show 

       

      BudgetActualsTBBudget
      B2018

      10

      100-10=90

      B201820100-30=70
      B201830100-60=40
      B202140200-40=160
      B202150200-90=110