I intent to calculate month to month changes (additions and reductions) and display them on a waterfall chart. However, the comparison needs to happen on a row level as each row respresents a unique material number. There is one row per material number in each month if the value of stock for that material in that month is more than zero. If material has no stock, that material number does not show up. This calculation should also work upon applying a column filter. The powerBi file is attached. Forum question_2706.pbix (37.9 KB)
In addition, a general sketch of the intended output is also attached. It doesn’t need to look exactly like shown as long as numbers can be presented in any other acceptable form like a bar chart or a table.
Hi @a_nimish - Are you looking to display data Date wise as shown in example. Can you confirm how are you calculating Reduction/new as it is not available in the sample PBIX provided.
Yes. I would like to display date-wise (which is month end basically).
Regarding the calculation, I need to calculate as follows.
If a material SCTO14103001 had a value of 50,000 on Feb 28, 2023. If the value reduces to 42,000 on Mar 31, 2023. The change should be -8,000. The total of all such reductions in different material numbers would be shown in red. If a material does not show up in next month, that would also be considered a reduction.
If a new material gets added in a particular month which did not exist in earlier month oo if the value of an already existing material increases compared to the earlier month, it should be totalled under “new” in a green display.
I was not able to make that calculation in a new column. I am not sure if I need to bring previous value in a new column and compare the two to calculate the change. I also don’t know how I can bring previous value in a new column.
I hope I was able to clarify. If not, please let me know.
Hi @a_nimish - Please check if the solution provided in attached file satisfy your problem. For this, I have created a new Index column in Power Query and then created new columns using Dax to apply logic as mentioned by you.
Thank you for your effort. I really appreciate it. It is quite close to the solution, but there are a couple of small issues I need to clarify.
Having seen the outcome, I have realized that the “Impairment %” is an important column and the calculation should work with this filter. When I apply the filter of 90%, I would like to have the addition of 50,000 in April 2023 due to SCTO14103001 and reduction of 17,500 due to MQRG10824991 and FWWW10567001.
BCTA10336001 only exists in Feb and Apr. There is no line in March. Therefore, it should be calculated as reduction of 20,000 in Mar and addition of 22,000 in Apr. Could we achive that?
FYI - In my real model, I am using a date dimension table that is linked to the report date column.
I am sorry if I am making it more confusing. If you have any questions, let me know.
Regarding index column, you have applied some grouping technique as follows:
= Table.Group(#“Changed Type”, {“Report Date”}, {{“AllRows”, each _, type table [Report Date=nullable date, Material Number=nullable text, Value=nullable number, Impairment=nullable number]}})
Is that done using M-query or is there a possibility to generate this command using transformation menu? Could you please guide?
Hi @a_nimish - First of all I am not sure what are you looking for with this new requirement. Secondly, as this is a completely new requirement, request to close this post and open a new one. There you can explain the requirement in detail.
Regarding Power Query steps, yes these are straight forward ones applied using Power Query Editor (Transformation Menu). You can click on highlighted button next to each step to see what is being done.
This is not a new requirement. I have only provided additional information. The picture attached in my original post provides the expected output. In the solution you have provided, that is not yet the case, though it is on the right lines. Therefore, I have clarified a couple of topics further to clear the understanding.
Hello @a_nimish, can you kindly upload a Power BI Desktop File that accurately reflects your question as a fresh question? With multiple reviews, the solution provided by @ankit follows your question and provides a reasonable solution.
Providing constructive criticism:
With that in mind, please ensure that the file includes the desired visualizations and slicers, and not just a table of columns and rows. Your initial question lacked clarity and did not provide any metadata descriptions, despite @ankit’s well thought out solution as a trusted volunteer member of the EnterpriseDNA Community Forum. It’s important to note that our community forum consists of members who generously give their time and technical expertise on a best effort basis.
I have provided an exact replica of my powerbi source file in addition to the picture of the visualization I have in mind, the output it should show with the calculation and the filter to be applied. If I knew how to create that calculation and visualization in Powerbi, there was no need to seek support. Therefore, I am not sure what more I can provide here.
I fully understand the efforts members are taking to help out others to solve their problems in spite of their busy schedules and I have highest regard for that. I have also acknowledged that in each of my posts.
I won’t continue with my post here and will close it as advised.
Hi @a_nimish - I will take this as a learning on how to post in future that is to cover the requirements in details and not in parts. It’s not to discourage from posting or seeking help.
In this instance I took up the issue as based on the initial requirements, I estimated I will be able to do it in this much time which I was able to.
New requirement is not only increasing the complexity and will be time consuming also that may not be possible for me. Also, one more reason I asked to create a new post is as members don’t generally pick up the post that someone else is working on, so raising a new post will increase visibility and chances of getting response from other users.
You have already helped me a lot with your solution and I got to learn about how to use Groupby to create an index. I have only to gain from this interaction. There is no question of taking anything in a bad way.
