Forum Discussion

PBILearner2022's avatar
PBILearner2022
Helper I
2 years ago

Waterfall Chart - set as total

Hi PBI  experts, We have a process wise data that needs to be converted as a waterfall chart as below ,Also we wanted to know the how it would be for next 2 years eg: if it is 2024 it should have th...
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi PBILearner2022 ,

 

According to your description, I created these data.
Table1


Table2


1. Create a calculated column for table1.

Year = 'Table1'[Reason]&" "& YEAR('Table1'[Date])


2. Create different names for categories of different years, create index columns in order, and sort by index columns.


3. Create a calculated column.

Value1 = 
SUMX(
    FILTER(ALL('Table1'),
    'Table1'[Year]=EARLIER('Sort_Table'[Group1])),[Value])

Value2 = 
 SUMX(
    FILTER(ALL('Table2'),
    'Table2'[END]=EARLIER('Sort_Table'[Group1])),'Table2'[Value])

Test1 = 
var _step1=
CONTAINSSTRING(
    'Sort_Table'[Group1],"End"
)
return
SWITCH(
    TRUE(),
    _step1=TRUE()&&'Sort_Table'[Index]=MINX(ALL('Sort_Table'),[Index]),
    [Value2],
    _step1=FALSE(),[Value1])

Test2 = 
SUMX(
    FILTER(ALL('Sort_Table'),
    'Sort_Table'[Index]<=EARLIER('Sort_Table'[Index])),[Test1])

Result = 
IF(
    [Test1]=BLANK(),
    [Test2],[Test1])


If these do not meet your expected results, can you describe where the 350 in January 2024 in the figure comes from? Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.