Forum Discussion

VinhDam's avatar
VinhDam
New Member
10 years ago
Solved

Sum with Grouping

Hi everyone,   I have a simple table like below:   Sales  Aging $100   1 $200   2 $300   5 $400   9 $500   15   I would like to to perform different sums based on aging, for example, sum o...
  • greggyb's avatar
    greggyb
    10 years ago

    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.

Sean's avatar
Sean
Community Champion
10 years ago

You can of course create Calculated Columns based on other columns!

 

What's in the column [DaysOverdue] is it a Number? Days Overdue implies it already calculates today()-[DueDate]

 

If this is the case and you already have a number - this is all you need

Aging = IF(jvw_Bill_AR_List[DaysOverdue]<=30, "0-30​", 
        IF(jvw_Bill_AR_List[DaysOverdue]<=60, "31​-60", 
        IF(jvw_Bill_AR_List[DaysOverdue]<=90, "61-90",
        "Over 90") ) )

Let me know if this works!

 

MojoGene's avatar
MojoGene
Post Patron
10 years ago

Sean:

 

That worked fine. I'll have to go back and parse my earlier command very carefully to figure out where I went wrong. 

 

Thanks again.