Oh man, I know that feeling exactly. When I’ve been stuck like that, other folks on the forum have always been there to help me out, so it’s nice to be able to do the same for others. Glad that revised solution looks like it’s going to work well for you. If we need to polish it up around the edges to get it working perfectly for your particular model, just let me know.
Here’s a tip that’s worked really well for me in situations like this one - when faced with a data structure that you don’t know how to handle in Power BI, like the Revenue Scaling Factor table, think “how can I take a wide and short structure (lots of columns, few rows) and make it narrow and long (few columns, lots of rows)?” DAX handles the latter beautifully, and the former quite poorly. Once we unpivoted the scaling factor table to make it a narrow and long structure, the DAX reduced down to a pretty basic CALCULATE/FILTER structure (but for the extra rigmarole with the matrix totals). Here’s another example where “narrow and long” made life much simpler:
It won’t solve all your problems, but I’ve been surprised how often it’s the key to cracking a difficult solution.
- Brian