Forum Discussion

katietran0467's avatar
katietran0467
Icon for Helper I rankHelper I
1 year ago
Solved

Detailed Budget Allocation Based on Actuals

Hi everyone, I'm facing a challenge with calculating budget allocations based on actual data. Here's the situation: We have actual figures at a detailed level, but only have monthly budget totals ...
Need Help
  • katietran0467's avatar
    katietran0467
    1 year ago

    Hi everyone,

     

    I figured out the solution based on the concept of SQL view. I created a temporary view to be able to use SUMX.

    Here is the final measure. Hope it helps other members or me in the future.

     

    _DetailBudget = 
    var _table = FILTER(
    SUMMARIZECOLUMNS(
    'Month'[YearMonth], Account[Account], "Amount", MIN('Fact'[Amount]),
    "_Multipler", DIVIDE(CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Debit", ALL('Account')), CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Credit", ALL('Account'))),
    "_New", MIN('Fact'[Amount]) * DIVIDE(CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Debit", ALL('Account')), CALCULATE(sum('Fact'[Amount]), 'Fact'[Type] <> "Credit", ALL('Account')))
    )
    , 
    Account[Account] <> "Budget")

    RETURN
    SUMX(_table, [_New])

     

     

     

Ashish_Excel's avatar
Ashish_Excel
Icon for Solution Supplier rankSolution Supplier
1 year ago

Hi,

Share some data to work with and show the expected result very clearly.  Share data in a format that can be pasted in an MS Excel file.

  • katietran0467's avatar
    katietran0467
    Icon for Helper I rankHelper I
    1 year ago

    Hi Ashish_Excel 

     

    Here is the data

    YearMonthAccountAmountType

    202501HR23Debit
    202501IT15Debit
    202501BD54Debit
    202501Budget100Credit
    202502HR26Debit
    202502IT19Debit
    202502BD48Debit
    202502Budget95Credit
    202503HR17Debit
    202503IT36Debit
    202503BD45Debit
    202503Budget89Credit

     

    And here is the expected outcome (red total figure)

     

     

     

     

    • Ashish_Excel's avatar
      Ashish_Excel
      Icon for Solution Supplier rankSolution Supplier
      1 year ago

      Cannot understand anything there.

    • v-sgandrathi's avatar
      v-sgandrathi
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi katietran0467,

      Thank you for sharing the data and the expected outcome.
      I'm attaching the .pbix file, please have a look and let me know if this aligns with what you were expecting.

       

       

      I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

       

      Thank you.

      • katietran0467's avatar
        katietran0467
        Icon for Helper I rankHelper I
        1 year ago

        Hi v-sgandrathi 

         

        Thanks for your time to investigate it.

        I checked your file but the total number is still incorrect. The DetailedBudget should be equal to TotalBudget = 284 in total, not 569 as your file.

        To work around, I created a view by SUMMARIZECOLUMN and put numbers to use SUMX.

        If there is a way using DAX calculation without creating a view, it would be a better solution.