Forum Discussion
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.
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
AnkitBISolution Sage
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.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.