Forum Discussion

CYParker's avatar
CYParker
Advocate II
4 years ago
Solved

Store Calculated Values into table

Hi All,   I have a set of raw bank account transaction data. I'd like to generate monthly totals of debits and credits for use in some sort of time series visual.   My idea is to have a new table...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi CYParker ,

     

    I feel like you need a summary table based on your description?

     

    You can use the New Table function provide by Power BI via SUMMARIZE( ).

    For example, the data looks like this:

    Table expression:

     

    Table =
SUMMARIZE (
    'Fact Table',
    [Date].[Year],
    [Date].[Month],
    "Total Expense", SUM ( 'Fact Table'[Expense] ),
    "Total Debit", SUM ( 'Fact Table'[Debit] )
)

     

    Result:

    This expression also works in measure. What's difference between measure and calculate column, please refer this:

    Calculated Columns and Measures in DAX - SQLBI

     

    When you refresh your data, the new calculation table is recalculated, however, it does not change when you do some interaction with any of the visuals in the report. report can only fetch data from the model(table), it cannot return data. So, you cannot transcribe some values into the model(table) for any interaction you do.

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello CYParker ,

The simple solution to your problem is that you need to add the 'Year-month' column in your new table instead of the year only so that your data will be calculated for each month automatically. So it will remove your manual work.

Please let me know if you need more help.

Thanks,
Neel

  • CYParker's avatar
    CYParker
    Advocate II
    4 years ago

    Hi Anonymous,

     

    Could you explain how the data is calculated? 

     

    A large part of my question is how to get the Total Expense and Total Income values into the table

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi CYParker ,

       

      I feel like you need a summary table based on your description?

       

      You can use the New Table function provide by Power BI via SUMMARIZE( ).

      For example, the data looks like this:

      Table expression:

       

      Table =
SUMMARIZE (
    'Fact Table',
    [Date].[Year],
    [Date].[Month],
    "Total Expense", SUM ( 'Fact Table'[Expense] ),
    "Total Debit", SUM ( 'Fact Table'[Debit] )
)

       

      Result:

      This expression also works in measure. What's difference between measure and calculate column, please refer this:

      Calculated Columns and Measures in DAX - SQLBI

       

      When you refresh your data, the new calculation table is recalculated, however, it does not change when you do some interaction with any of the visuals in the report. report can only fetch data from the model(table), it cannot return data. So, you cannot transcribe some values into the model(table) for any interaction you do.

       

      Pbix in the end you can refer.

      Best Regards

      Community Support Team _ chenwu zhu

       

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

       

      • CYParker's avatar
        CYParker
        Advocate II
        4 years ago

        Hi,

         

        Thanks for your response and apologies for not replying sooner.

         

        I havent tried your solution as yet, but it definitely looks like it's what I'm looking for. Once I get time to try it out I'll report back.

         

        Cam.