Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Natrify
Frequent Visitor

Help needed for Waterfall chart with binary values

Hi all, 

 

I'm trying to put together a waterfall chart showing increase and decrease on the count of a binary value from a dataset. Here is an example of what the data looks like: 

Natrify_0-1716953650696.png

I Want to count all the '1' values for each month. then create a waterfall which shows the increase/decrease in '1's for each month. 

Im hoping for something that looks like this:

Natrify_1-1716953742801.png

But with count intead of dollar values. 

So Far the problem I'm having is that I get a count, but the chart just shows a total for each month and lists that as each months bar, its not showing increase/decrease month by month:

Natrify_2-1716953820965.png

 


I've provided some mock sample data, nothing here is confidential. If you have any ideas, help is much appreciated. 
 
Sample_data file: https://drive.google.com/file/d/1zfg0jh0ZFc-p_b5JHG_yqLFOXKxoiQI0/view?usp=sharing 

1 ACCEPTED SOLUTION
Natrify
Frequent Visitor

I've figured out a way to do what I want. First of all I neded to make a month column in the data.

Month = FORMAT(Data[Date], "yyyy, mmmm")

Change the format of the column to date: 

Natrify_0-1717130780569.png

Next I made a Dax code that gets previous month, and current month then calculates the difference, I needed to us parallel period for this: 

Diff month-on-month = 
VAR LM = CALCULATE(SUM(Data[Binary]), PARALLELPERIOD(Data[Month], -1, MONTH))
VAR CM = SUM('Data'[Binary])

RETURN 
CM - LM

Since What I need to count is in binary format, I can use SUM to get only the '1's and filter out the zero values.

Next I create a waterfall, add in my month-on-month measure into the y-axis, and add in the month column into the category field. Whats important here is to make sure you aren't using the hierarchy format for the Waterfall visual: 

Natrify_1-1717131027470.png

 



View solution in original post

2 REPLIES 2
Natrify
Frequent Visitor

I've figured out a way to do what I want. First of all I neded to make a month column in the data.

Month = FORMAT(Data[Date], "yyyy, mmmm")

Change the format of the column to date: 

Natrify_0-1717130780569.png

Next I made a Dax code that gets previous month, and current month then calculates the difference, I needed to us parallel period for this: 

Diff month-on-month = 
VAR LM = CALCULATE(SUM(Data[Binary]), PARALLELPERIOD(Data[Month], -1, MONTH))
VAR CM = SUM('Data'[Binary])

RETURN 
CM - LM

Since What I need to count is in binary format, I can use SUM to get only the '1's and filter out the zero values.

Next I create a waterfall, add in my month-on-month measure into the y-axis, and add in the month column into the category field. Whats important here is to make sure you aren't using the hierarchy format for the Waterfall visual: 

Natrify_1-1717131027470.png

 



v-xiandat-msft
Community Support
Community Support

Hi @Natrify ,

When you calculate the first of the month, he can easily figure out the.However, the calculation of 1 for the previous month does not show up in the waterfall plot, due to the insignificant difference between the previous month and this one.

If you calculate the difference between 1 and 0 for the current month, it is calculable and shows up normally in the waterfall charts.

vxiandatmsft_0-1717038543263.png

Best Regards,

Xianda Tang

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors