cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sleepeatviz Frequent Visitor
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

NameValue
A10
B-5
C20
D-10

now my waterfall chart looks like this

 

Capture.PNG

 

 

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
sleepeatviz Frequent Visitor
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
Highlighted
AnkitBI Member
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.

Image1.PNG

 

 

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

Image2.PNG

 

 

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.

Image3.PNG

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

 

 

sleepeatviz Frequent Visitor
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

AnkitBI Member
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.

sleepeatviz Frequent Visitor
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!

AnkitBI Member
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 

a110
b2-5
a,b35
c420
d5-10
c,d610

  

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)) )
sleepeatviz Frequent Visitor
Frequent Visitor

Re: Need help to customize default water fall chart

@AnkitBI,

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!

 

 

AnkitBI Member
Member

Re: Need help to customize default water fall chart

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

sleepeatviz Frequent Visitor
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,

 

AnkitBI Member
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. 

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 33 members 774 guests
Please welcome our newest community members: