How can I create a dynamic summary table to include in a DAX measure?

[Anonymous]

I'm trying to create a calculated table that responds to slicer(s) selections and sums spend, grouped by FiscalYear. I want to use columns from this table as arguments to the XIRR function.

I think I could do this if with one table but the columns I need are in different tables in my model. I have a fact table with figures for spends for the next 50 years for about 100 projects:

ProjectCode    FiscalYear   Spend
1              2019         300000
1              2020         150000
1              2021         25000
2              2019         100000

I have a project attribute table with the dimensions I wish to slice:

ProjectCode    Region       Type
1              North        Buyout
2              South        Bank account
3              East         Mortgage
4              West         Rent

I've tried a calculated table:

SUMMARIZECOLUMNS(FACT[FiscalYear],"Total Cashflow", SUM(FACT[Spend]))

This does produce the result I want but it doesn't change in response to slicers. For example, if I slice on Region = North, I would expect to see the summary table for ProjectCode = 1 only but it still summarises all the data.

I am not fully understanding the syntax I think. Any help much appreciated. Thanks.

[Icey · Accepted solution]

Hi @Anonymous ,

The cause is as what @amitchandak said.

You can try to create a measure and then create a table visual.

Total Cashflow = SUM(FACT[Spend])

Best Regards,

Icey

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

[amitchandak]

@Anonymous , once you create a table it will not respond to the slicer. Only a table inside measure formula as a variable respond to slicer changes