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.

MojoGene's avatar
MojoGene
Post Patron
10 years ago

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.

MojoGene's avatar
MojoGene
Post Patron
10 years ago

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? 

 

  • MojoGene's avatar
    MojoGene
    Post Patron
    10 years ago

    Sean:

     

    Looks like I have found and answer. The "DaysOverdue" column was itself a calculated column. I was able to substitute another column in the table ([DateBilled]), which is an original column from the underlying SQL table. That solution worked perfectly.

     

    So, am I to conclude that you cannot make a calculated column based on another calcuated column?

    • 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.