0

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.

deethreenovice
  • 127
  • 1
  • 2
  • 17
  • Calculated tables can not respond to slicers. You need to write DAX measures. – RADO Jun 30 '20 at 16:29
  • OK thanks. That explains it. I was planning to include the table in a measure; the calculated table was just so I could "see it", before I wrote the measure. So if I store the dynamic table in a variable, and then pass columns from it, should work? – deethreenovice Jun 30 '20 at 16:40
  • Yes, that's how it's done usually. For example: https://stackoverflow.com/questions/61390142/sum-where-version-is-highest-by-another-variable-no-max-version-in-the-whole-da/61391944#61391944 – RADO Jun 30 '20 at 18:53

0 Answers0