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
Schoif-D
Helper I
Helper I

Waterfall Chart not using dates

Hi

 

Im very new to Power BI, literally only started playing with it yesterday, and there is a chart i use to do in excel, that i would like to replicate in Power BI, but, im finding this difficult and every help page i search on Google just shows me that the X Axis is always in a date range, i have tried to create a Pie, Bar and Donut chart then swapping to Waterfall but it doesnt work, how can i create this chart below in Power BI?

 

Capture.PNG

2 ACCEPTED SOLUTIONS

@Schoif-D

I see in your dataset, the first step should be doing a unpivot.

 

Capture.PNG

 

Then replace the currency symbol with blank and convert that column to whole number.

Capture.PNG

 

In the waterfall chart, use the attribute and value column.

Capture.PNG

 

As to the axis order in the waterfall chart, check this thread Sorting Data Manually.

Check more details in the attached pbix file.

 

View solution in original post

Add columns through the query editor uses M language not DAX (click "Advanced Editor" in the query pane to see the M code)

 

If you instead create a calculated column on the report layout page, your code is accurate.

 

Hope this helps

David

View solution in original post

14 REPLIES 14
Schoif-D
Helper I
Helper I

To all of you @dedelman_clng @Eric_Zhang @Sean and @Phil_Seamark thank you all for your help its finally how i want it to look, all except the total bar, but im not stressing over that for now, thanks again

 

Capture.PNG

@Schoif-D

It is great to hear you finally figured it out. If no further questions, you can accept the replies making sensing as solution to close this thread. For any new questions, you can raise another thread.Smiley Happy

Phil_Seamark
Employee
Employee

Hi @Schoif-D,

 

Can you please post some sample data and we will do our best to help you.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil

 

How do i upload a data file? i can see how to upload photo/video


@Schoif-D wrote:

Hi Phil

 

How do i upload a data file? i can see how to upload photo/video


@Schoif-D

You can upload things to a network drive(Onedrive, Dropbox etc) and then share the link.

Almost too easy Smiley LOL i thought there may be a attachent option like on excel forum, I have now uploaded an example data set into onedrive, im going to carry on trying in the desktop to see if i can build a data table to replicate Seans example, however im trying to run the chart straight from the data set as there is a lot of charts and tables that will be generated by this and linked to slicers, and dropdowns

 

https://1drv.ms/u/s!AhMWRjYUcisAhAV7Nl_vgRU_q4yK

 

Thanks

@Schoif-D

I see in your dataset, the first step should be doing a unpivot.

 

Capture.PNG

 

Then replace the currency symbol with blank and convert that column to whole number.

Capture.PNG

 

In the waterfall chart, use the attribute and value column.

Capture.PNG

 

As to the axis order in the waterfall chart, check this thread Sorting Data Manually.

Check more details in the attached pbix file.

 

Ok so that works fine thank you all, except the cost elements should be minuses/decreases, so sales and margin should be positive the rest multiplied by -1

 

I tried to insert a column into the table using this formula

 

IF(AND([Attribute]="Net Sales Amount",[Attribute]="Gross Margin"),[Value],[Value]*-1)

but its telling me that "IF"  wasnt recognized?

Add columns through the query editor uses M language not DAX (click "Advanced Editor" in the query pane to see the M code)

 

If you instead create a calculated column on the report layout page, your code is accurate.

 

Hope this helps

David

That was so much easier doing the formula in the report layout, took less than a minute, youre right i was trying to do that formula in the query editor

 

Thank you

Ok David thank you, im use to VBA and Excel formulas, am very new to Power BI and DAX, amd M and R, so am itrying to get my head around it, thank you for explaining the problem, i will give it a go see if i can get it done 🙂

Ok great thank you @Eric_Zhang i will give that a go 🙂

Waterfall Chart2.png

EDIT: The one thing about this Chart in PBI if I recall there's no option to turn off the Total...

Hi Sean

 

Thats how i want it to look, obviously the total is a nuisance, was this done by creating a data table in the desktop? i can give that a go see if i can connect it it my main data set im using

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