cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anthony_W Frequent Visitor
Frequent Visitor

Wrange data for Waterfall Chart

Hi All,

 

I am trying to wrangle my data into the format required for a waterfall chart. I can calculate a waterfall chart that just shows the difference in values from zero, but I would like to include the initial value in the chart. The table below has an example of my data.

Year-Quarter

xParameter

Difference

Year Quarter First

XParameter First

2020-Q1

38.21

38.21

2020-Q1

38.21

2020-Q2

36.99

-1.22

2020-Q1

0

2020-Q3

37.31

0.33

2020-Q1

0

2020-Q4

39.09

1.78

2020-Q1

0

2021-Q1

38.28

-0.81

2020-Q1

0

2021-Q2

39.03

0.75

2020-Q1

0

2021-Q3

39.55

0.51

2020-Q1

0

2021-Q4

38.41

-1.14

2020-Q1

0

 

If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then.

 

The Calculation for Difference is :

Difference =
CALCULATE (
    [xParameter]
        - CALCULATE ( [xParameter], PARALLELPERIOD ( DateTable[Date], -1, QUARTER ) )
)

 

I have tried to introduce an adjustment to account for the first xParameter value, but this always returns to the first value at 2020-Q1. For the example above, I would want the XParameter first value to show at 2021-Q1.

 

XParameter First =
IF (
    FIRSTNONBLANK ( DateTable[Year-Quarter], [Year-Quarter] )
        = FIRSTNONBLANK ( ALL ( DateTable[Year-Quarter] ), [Year-Quarter] ),
    [xParameter],
    0
)

The Year-Quarter is in a Date Table and filtering is done using other columns in the date table e.g. Year.

 

Any ideas of how to change the formulae to account for filtering the Year-Quarter Column? 

Thanks.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Wrange data for Waterfall Chart

@Anthony_W ,

 

I couldn't reproduce the senario below, if possible, could you please provide some screenshot(Do mask the sensitive data) or provide a sample file?

 

"If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then."

 

Community Support Team _ Jimmy Tao

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

Anthony_W Frequent Visitor
Frequent Visitor

Re: Wrange data for Waterfall Chart

v-yuta-msft,

see link below for an example sample file.

Link to Waterfall example

 

The waterfall chart is OK when a year isn't selected. When a year is selected, the waterfall chart starts at zero, rather than the first ash value. Hope that makes sense!

 

Cheers,

 

Anthony

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 318 members 3,353 guests
Please welcome our newest community members: