Sum with Grouping

[VinhDam]

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 of Sales if Aging is between 1 and 2; sum of Sales if Aging is between 2 and 5; or sum of Sales if Aging > 5

How would I construct such a report?

Many thanks for your help.

[JPotwade]

Hello Team,

I am facing issues in calculating percentage as per the dimension.

I have 2 dimension Age and Gender. First i Want to calculate the growth% based on Gender and then i want to calculate Growth% based on Age.

Problem is Growth% i am calculating runtime.

Can i get the solution?

[gnjago]

Can you provide details with some example into how it could be done dynamically?

[VinhDam]

Hi greggyp, smoupre, and itchyeyeballs,

Many thanks for your help.  I was clueless on DAX and Power Query the other day.  Now I have something to work on!  I do appreciate your help.  I will try my best to learn DAX and Power Query but programming is not my expertise so if I get stuck again, I hope you would give me more pointers to work on.  🙂

Best regards,

Vinh Dam

[VinhDam]

Hi everyone again,

I am being tasked with producing some financial reports using Power BI.  One of the reports is Receivable Aging.  The two columns to be used in the Receivable Aging report are Amount Due and Due Date.  The Aging periods are 0-30 Days, 31-60 Day, 61-90 Days, Over 90 Days.

I was able to extract the aging days, DurationDays, for each row, but I don't know how to create new tables for each of the aging periods.

My previous example has been simplified.  The example below is much more realistic:

Amount Due     Due Date   

$100                 1/16/2016 

$200                 2/16/2016

$300                 5/14/2015

$400                 7/25/2014

$500                 1/9/2016

$600                 5/12/2015

$700                 6/4/2016

Expected Report:

Aging       Amount

0-30         $500

31-60       $0

61-90       $0

Over 90    $1,300

Also, would you recommend a book on DAX, Power Query, with an emphasis on programming?  

Many thanks for your help again.

[Greg_Deckler]

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.

http://community.powerbi.com/t5/Community-Blog/Correlation-Seasonality-and-Forecasting-with-Power-BI...

[MojoGene]

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.

[Sean]

@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

[MojoGene]

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]

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]

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]

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]

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.

[greggyb]

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

[Greg_Deckler]

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

[greggyb]

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

[greggyb]

Create a new field using Power Query when you're importing data.

Add a custom column using the following Power Query code:

let
  diff = Number.From( DateTime.Date( DateTime.LocalNow() ) - [DueDate] )
  ,Bucket =
    if diff < 31
    then "0-30"
    else if diff < 61
    then "31-60"
    else if diff < 91
    then "61-90"
    else "Over 90"
in
  Bucket

This assigns the difference in days between the date of model processing (assuming you process daily, this means today's date) and the due date to the local variable, diff.

Bucket is then assigned a text value based on the value of diff. This gives you labels which can be used for any measures in your model.

I'd recommend assigning an integer key to each of the buckets and having the labels in a separate dimension, but that's up to you. You can use the same logic for both key and label.

[VinhDam]

Hi Greg,

Since the function DateTime.Date(DateTime.LocalNow() - [Due Date]) will also return negative values--payment not due yet, I have to take out all the negative values.  I change the condition to "if ((diff = 0) and (diff < 31))" but it did not work.  What is the correct syntax for this "if and" expression?

Thanks

[greggyb]

You can just add one more step to the if-then chain of statements in my originally suggested Power Query code:

let
  diff = Number.From( DateTime.Date( DateTime.LocalNow() ) - [DueDate] )
  ,Bucket =
    if diff < 0
    then null
    else if diff < 31
    then "0-30"
    else if diff < 61
    then "31-60"
    else if diff < 91
    then "61-90"
    else "Over 90"
in
  Bucket

'let' defines a local namespace, in which we can assign values to variables. The first we assign is the variable name 'diff' to the result of the calculation of the number of days between today and the due date.

We then refer to the value represented by 'diff' repeatedly in our definition of the next variable, 'Bucket'. Bucket stores a null or a text string based on the result of evaluating the if-then-else chain of statements, terminating on the first condition that returns true. I simply added one more check before all the others for a 'diff' value of < 0, in which case we return null.

to end the let statement, we use 'in', where we can define the output of the result of some calculation in that namespace. In our case we return the value of the variable 'Bucket'.

This entire statement is run once per row in the table, with the let statement being evaluated in the context of the values in that row.

[VinhDam]

Hi Greg,

Thank you for the answer and for being so thoughtly.  I am going to digest the explanation now.  🙂

[Greg_Deckler]

Couple different ways of doing this.

One, you could add a slicer for Aging and any other visualization, perhaps Card for Sales. You can use Ctrl to click multiple values in your slicer and see the summed result of sales (default aggregation).

You could add multiple visualizations for Sales (default sum aggregation) and put filters on each visual to segment out the buckets, the first one for aging values <= 2, second for aging values between 2 and 5 and for those > 5.