Forum Discussion
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 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.
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.
22 Replies
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.
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.
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.
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 BucketThis 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.
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
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.
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.
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
Can you provide details with some example into how it could be done dynamically?
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?
