Dynamically Re-calculate Calculated Column (work-around?)

[Anonymous]

From what I've gathered you can't re-calculate a calculated column based on something like a What-If parameter.

So I'm wondering if there's another solution for my problem.

I have a campaign table where I have a calculated colum for the total ROAS for each campaign. I have then created a number of ranges of different ROASs (0-1, 1-2, 2-5), and I also have a spend column. This way I can create a table and view how much has been spent on each ROAS range.

Here's the problem: 

I want to be able to slice this table in different years to view how much was spent on that ROAS range in that given year, but I want the year I select to re-calculate the ROAS ranges based on the performance of that given year. If I take my current tables and slice on a year, it will simply tell me what was spent on that ROAS range that year. But the ROAS range is in this case rigid so its taking the entirety of my data tables to calculate the ROAS ranges and then giving me the spend that was assorted to those ranges within the year I have selected.

I feel like I may need to tackle this differently but kinda stuck atm.

Thanks

[mahoneypat · Accepted solution]

It takes some more advanced DAX but you can do this.  You can create virtual tables with virtual columns in a measure; the columns can use those ranges in their calculation.  The ranges can be harvested from an ROA table, or be calculated dynamically based on another measure (max value, etc.); I couldn't tell from your post which scenario you have.  You can then do analysis on those virtual columns in that virtual table.

This blog shows an example - https://blog.enterprisedna.co/segment-your-customers-into-groups-advanced-dax-example/

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks!