Forum Discussion
Binning a measure
I am trying to create a measure or a column that will create groups of a column. This measure is the number of days of a project that I have already established using datediff. The days vary from 1 day to 160 days. All I want is to achieve these groups and their frequencies. This was however achieved in another method here.
https://community.powerbi.com/t5/Desktop/Date-Difference-with-a-switch-between-two-dates-and-One-date-and/m-p/2056215#M768354 . However, this is not working as perfectly when I add other columns like the project cost in all the projects.
Whichever other methods is highly welcome. Thanks
Anonymous see attached and tweak as you see fit.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
3 Replies
- parry2k
Super User
Anonymous it is a bit unclear what you are trying to achieve. I would recommend pasting sample data with the expected output. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Thanks, parry2k
I have a dataset (Table1) with the following sample data.Project ID Date Started date completed Amount used 1 04/02/2021 27/02/2021 34,533,200 2 07/02/2021 34,095,600 3 23/03/2021 07/04/2021 27,563,760 4 31/03/2021 05/05/2021 19,456,980 5 03/05/2021 13,904,710 6 01/06/2021 10,543,600 The task is to get the difference in days from start and end of the project. As you can see, some projects do not have a date completed. Hence for these ones, one should get the difference between the Start date and today so as to know how long these projects have taken before completion.
The next task is to bin/group these projects by the time they have taken so far or they took to complete if already completed. The groups are already predetermined and are as follows.0-30days, 30-60 days, 60 -90 days, More than 90 days.
The ultimate solution is to have a table like this one.
Group project ID Frequency Amount Used Total 0-30 days 7 100,345,230 30-60days 3 65,677,900 60-90 days 2 23,567,677 More than 90 days 0 0 I hope this is quite understandable now. Thanks.
- parry2k
Anonymous see attached and tweak as you see fit.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡