cancel
Showing results for
Did you mean:
Frequent Visitor

Need help to customize default water fall chart

Hi Gurus,

I need some help regarding creating a calculated cloumn in dax or any other way which suits.

I have a name cloumn which have 4 values: A,B,C,D

 Name Value A 10 B -5 C 20 D -10

now my waterfall chart looks like this

but my requiremnt is to show some sub totals on the way to show the final total. for example I need to show a total bar of A+B after the B and before C in the X axis.  I will need C+D again as a sub total before the final total. I was thinking in the line of created a calculated cloumn based on the Name cloumn. But not able to get my head around it since if else logic is not working.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Re: Need help to customize default water fall chart

ok..creating a category will do that..but what if you have hierical category? for example if you want to create 3 categoris in the name cloumn. i,e A=group 1,B=group 2, A+B=group 3

I could not achieve this calcualted category in dax using if else statement since when we will be checking the value of each row is eitheir A or either B..it will place it in group 1 or group 2 all the time...any idea if this can be achieved through any function in dax?

Cheers,

9 REPLIES 9
Member

Re: Need help to customize default water fall chart

@sleepeatviz Below is the solution I have achieved , bit lenghty but might work for you. Only problem I believe will come with Total as it will show accumulative and don't think we have control over it. Though will check.

1) Create a new table (Table 2 in DAX), with Column containing name and SubTotal. Also, add Sort Order column for Sorting.

2) For sample, I have created a Table (Table 3) with your actual values

3) To get Subtotals, add Calculated column in Table 2 using below dax. This will give you original and Sub-Total values.

`Calculated Column = CALCULATE(sum(Table3[Column2]),filter(Table3,Table3[Column1] = left(Table2[Name],1) || Table3[Column1] = RIGHT(Table2[Name],1)) )`

4) Below is the new Waterfall chart using data from new table.

Regards
AJ
Do Like Post if response seems good and Worth liking.
Do Mark as Solution if response resolved your Issue.

Frequent Visitor

Re: Need help to customize default water fall chart

@AnkitBI Ankit,

can you share me the PBIX file if possible. I am not clear creating the table part.

Thanks

Member

Re: Need help to customize default water fall chart

PBIX. Basically what I did is, in new table I added a Sub-Total cell for each set. For example, For sum of A&B I set up A,B, similarly C,D

In DAX, I am extracting values from Source Table for Sub-Total field.

A,B = (A + B) i.e 10 - 5 = 5

C,D = (C + D) i.e. 20 - 10 = 10

Regards
AJ
Do Like Post if response seems good and Worth liking.
Do Mark as Solution if response resolved your Issue.

Frequent Visitor

Re: Need help to customize default water fall chart

@AnkitBI

Thanks a lot for your reply.But I am still not able to understand your filtering logic in the DAX. can you please explain me.Sorry for being a complete noob out here!

Member

Re: Need help to customize default water fall chart

I will give it a try. I hope you understand Calculated Column works on Row By Row basis.

For each Row in Table2, I am taking sum of values from Table3 using CALCULATE(sum(Table3[Value]). To identify rows from Table3 am using Filter. Filter is simply saying, get me all rows from Table3 where.

1) Table3[Name] is either equal to First character of Name in Table2

2) Table3[Name] is either equal to Last character of Name in Table2

For Row 1 in [Table2] i.e. 'a', Logic will get only single row from Table3 as first and last character are same. i.e. 'a'. Similarly for Row 2 i.e. 'b'.

For Row 3 i.e. a,b. Logic will get values for two rows from Table3 i.e. Left Most character 'a' and Right Most character 'b'. After filtering the values it will perform sum i.e. your SubTotal of A and B.

Name    Sort Value Calculated Column

 a 1 10 b 2 -5 a,b 3 5 c 4 20 d 5 -10 c,d 6 10

PS: I have changed Column Names of Table3 in below, so you may get better understanding.

`Calculated Column = CALCULATE(sum(Table3[Value]),filter(Table3,Table3[Name] = left(Table2[Name],1) || Table3[Name] = RIGHT(Table2[Name],1)) )`
Highlighted
Frequent Visitor

Re: Need help to customize default water fall chart

Great! Thanks for your added efforts to explain it.I can now understand your logic.

However,my only issue in this is that if the data set is small we can add this subtotal row manually.But if  the dataset is large (as in most business cases) is there any alternate you can think of?

Cheers!

Member

Re: Need help to customize default water fall chart

Unfortunately not, you need to have some category to show value against.

Frequent Visitor

Re: Need help to customize default water fall chart

ok..creating a category will do that..but what if you have hierical category? for example if you want to create 3 categoris in the name cloumn. i,e A=group 1,B=group 2, A+B=group 3

I could not achieve this calcualted category in dax using if else statement since when we will be checking the value of each row is eitheir A or either B..it will place it in group 1 or group 2 all the time...any idea if this can be achieved through any function in dax?

Cheers,

Member

Re: Need help to customize default water fall chart

I didn't get your new requirement. Can you share some sample data and what is the expected result.

Announcements

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 368 members 3,569 guests
Recent signins: