Conceptual Question - Grouping/Summing Amounts Depending on Report View

DAX DAX Calculations
1

First time posting and I have a conceptual question - mods, let me know if this needs to have more details and I will delete and repost.

I am creating a variance report where revenue adjustments can be attributable to one of two standard drivers (A & B) or a third driver which groups what are effectively “one offs” or prior period adjustments. What makes it complex is that one offs/prior period for any month can be classified as a standard driver (A or B) in a quarterly view or similarly one offs/prior period in a quarterly view can be classified as a standard driver (A or B) in annual view. Attached is a rough example of how the numbers work in reality.

Any advice on how I can set up the framework to handle this would be very much appreciated as I haven’t been able to quite wrap my head around where to start.

Variance Example.xlsx (12.5 KB)

2

Hi @goforlexi. Welcome to the community! I can’t wrap my head around what you’re looking for either. Would it be possible for you to prepare a small sample dataset in Excel and an Excel mock-up of the final output you’re looking for? That would give us more to chew on…
Greg

1 Like
3

Thanks for the feedback, Greg.

I’ve updated my original post with some additional information and a quick excel mock up that I think illustrates what I’m trying to solve for.

4

Hi @goforlexi.

Thanks for the sample. My first thoughts are that 3 different DAX measures, one each for monthly, quarterly, and yearly would allow you to apply the appropriate business rule to assign OO to A or B and would then be available for use in each of the 3 matrices you want. If the 3 initial measures work, then you might try to develop a single DAX measure that uses those 3 that could perhaps use the SWITCH( TRUE() function and the ISINSCOPE function. Not sure if this would work, or if it would work in a single hierarchical matrix with year, quarter, and month though, but at least an avenue to explore …

Hope this helps.
Greg

1 Like
5

Hi @goforlexi, did the response provided by @Greg help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

Hi @goforlexi , we’ve noticed that no response has been received from you since the 18th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!