Store Calculated Values into table

[CYParker]

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 (e.g. below) which is automatically populated with the appropriate data based on calculations from the raw transaction data.

Year

Month

Total Expense

Total Debit

I currently have a couple of visuals (see below) which show the respective total values, which are filtered with a timeline slicer, and another filter which excludes transactions with certain categories.

I'm thinking there must be a way to have the total values populated into the new table without me needing to manually obtain the totals (selecting each month using the timeline and then transcribing the totals displayed in my visuals), but am just starting my journey in Power BI and am not sure where to start.

Could anyone give me some ideas or a starting point for me to work form to find a solution for this?

[Anonymous]

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]

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 · Accepted solution]

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]

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.