Need help to customize default water fall chart

[Anonymous]

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.

[AnkitBI]

@Anonymous 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.

[Anonymous]

@AnkitBI Ankit,

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

Thanks

[AnkitBI]

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.

[Anonymous]

@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]

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)) )

[Anonymous]

@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]

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

[Anonymous · Accepted solution]

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]

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