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.
The DAX version of this is:
Aging = IF(TODAY()-[Due Date]<=30,"0-30",IF(TODAY()-[Due Date]<=60,"31-60",IF(TODAY()-[Due Date]<=90,"61-90","Over 90")))
One note, this DAX version, nor I believe the "M" version from greggyb will give you the $0 values. So if you only have buckets of "0-30" and "Over 90", you will not see the "31-60" and "61-90" categories with $0. There are a number of techniques around that, you could create a separate "Enter data" query to add in a $0 value that falls into each category and merge it with your data feed for example, then you would ensure that you have all of the categories listed but it wouldn't affect your final sums.
See the comments area from my recent blog post, there is discussion around good DAX resources, etc.
Smoupre: Appreciate all your help on this forum!
I am trying to follow your instruction here. My DAX command looks like this:
Aging = IF(TODAY()-jvw_Bill_AR_List[DaysOverdue]<=30,"0-30",IF(TODAY()-jvw_Bill_AR_List[DaysOverdue]<=60,"31-60",IF(TODAY()-jvw_Bill_AR_List[DaysOverdue]<=90,"61-90","Over 90")))
(The table is "jvw_Bill_AR_List" and the column is "DaysOverdue".)
When I do this, I encounter the error message:
"A single value for column "DaysOverdue" in table "jvw_Bill_AR_List" cannot be determined. ...(etc.)"
Any advice? Thanks.
MojoGene Are you trying to create a Column or Measure?
This should work if you are trying to create a Column!
Look at my answer here...
http://community.powerbi.com/t5/Desktop/Combining-2-different-if-statements-in-one/m-p/31388#M10802
Sean:
Bingo! That was it. As a novice in this, I am still perplexed by the difference between measures and columns.
Thanks for your help.
Sean:
I may have spoken too soon.
I was able to put in a calculated column with no syntax error appearing, but no matter what the value is in the DaysOverdue column the result returned is always "Over 120."
I examined the DaysOverdue column to make sure; indeed, there are plenty of values <120.
Any thoughts on wha tis happening?
