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
Spigaw
Helper II
Helper II

Waterfall sums and total messed up when categorizing

Hello,

 

I am working on waterfall chart showing a simple evolution between dates. Basically, here are the numbers I am looking to get:

Spigaw_1-1655471286283.png

However, when adding the data type in the breakdown, PBI transforms some + in -, or - in +, and the total is wrong:

Spigaw_2-1655471366314.png

 

You will find below the sample dataset I am using for this graph.

 

PeriodType Sum
01/12/2021Down 1-17
01/12/2021Up 127
01/12/2021Up 24
01/12/2021Up 3178,5
01/05/2022Down 2-3
01/05/2022Down 1-15
01/05/2022Up 15
01/05/2022Up 4437,5
01/05/2022Up 25

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Spigaw ,

You can use Type and Period as Category.

Output:

vyingjl_0-1655717621426.png

 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
TomasAndersson
Solution Sage
Solution Sage

So since you have multiple values of the same type, the program calculates the difference between them for each type in the second example. So “Up 1” becomes -22 as 5 is 22 less than 27, instead of 5+27=32. 

 

TomasAndersson_0-1655972225477.png

 

 

I would try to use the actual values you have (I guess these are some types of calculated deltas already?) if you want a waterfall, or do some variation with other types of visuals if you want to use these. With a waterfall, you right now get the difference of your difference.

 

I found a way using the data you have here, but not in a very effective way. I created a new table with rows so that each type had a entry both in 2021 and 2022. Then I used a calculated column to calculate the cumulative difference from the start (so that in May, the December values are added as well):

TomasAndersson_1-1655972225480.png

 


Using that column for the Sum in the waterfall gave the expected output, although “2022” would have to be interpreted as cumulative change, 2021+2022 which might be confusing for a user.

TomasAndersson_2-1655972225486.png

 

 

v-yingjl
Community Support
Community Support

Hi @Spigaw ,

You can use Type and Period as Category.

Output:

vyingjl_0-1655717621426.png

 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I finally used this simple solution, thanks for your help and the other contributors too!

 

Spigaw_0-1655990401731.png

 

Thank you for this answer! However, it does not totally resolve my case as I need to have the two totals (one for 12/2021 and another for 05/2022).

 

Is it possible to achieve?

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors