Forum Discussion
Sum with Grouping
Are the groups static, or do they need to be dynamic based on a user selection?
If they're static, it's easy. Make a new field (preferably at your data source if you can, else in a query (e.g. in Power Query) before the data is added to the Power Pivot model) with those groups. Then you can just create visualizations against the new field that contains the group.
If they must be dynamic based on user selection, you'll have to give us some more detail on what your expected use case is, and the solution will be a bit more complex.
Greg_Deckler Just FYI, the Tabular storage engine can perform better compression on "native" columns than it can on calculated columns. Generally it is best practice to perform all ET before the L into the data model.
greggyb No argument there, however, as an end-user focused tool, I think it is useful to present DAX solutions as well. DAX is a language that someone with passable Excel formula skills can master relatively easily. "M" on the other hand requires more of a coding background in my opinion and has a significantly higher learning curve.
I don't tend to be a fan of absolutes. If I am building a model for a customer, I have to take into consideration their ability to maintain it and extend it over time. In addition, there is the case where if I am going to have to use DAX, as is sometimes the case, I'll often end up doing it all in DAX from a simple maintainability perspective. Nobody likes to work on a product that is half written in C#, half in Java and half in Perl, that's just not enterprise grade.
I think it is a good idea to remember the target audience for this community, not everybody is a pure BI professional and there are multiple things to take into consideration rather than a few select people deciding on a "best practice". "Best practice" for IT professionals, sure, "best practice" for your standard business end user, not necessarily.
It's always good to have multiple options and perspectives.
Greg_Deckler, I apologize if I came off as if I were criticizing you. That was not my intention. You are consistently one of the most helpful posters on this forum, and I was just taking the opportunity presented by your comment to bring up a point that I find to be important.
To be honest, I expected that you would be aware of the tradeoffs between DAX calculated columns and "native" columns, but your comment prompted the thought, so I addressed it toward you so other readers would understand what I was replying to.
I agree that multiple options are important, as some things which are cumbersome in M are trivial in DAX and vice versa, so for any data professional (and I do not intent this term to be exclusively referencing IT resources or BI folk), it's worth learning both, even if only at the level of primitive operations on basic types.