waterfall chart

6 Topics

"Hacking" a waterfall chart
Hello all, I have adapted a code that I found in the link below to do some waterfall charts : Power BI & DAX Tutorial: Waterfall charts with beginning and end states in 5 minutes - YouTube It works pretty well. The second part of the code generate a waterfall chart showing evolutions of places for trainings offered in the next four weeks. I would need to change the way in which “Disponibles” (available) is shown. As we are talking about training places still available, it should be better to show it in its position but in green and positive. I know this is not logical for a waterfall behavior, nevertheless, somebody would have an idea to do this? The code : %RempSes_PEB = VAR Tot_Proposees = CALCULATE(SUM('03_Sessions1'[Max]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK() || '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_AnnulSession = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_Sessmaintenues = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR Inscrits = CALCULATE(SUM('03_Sessions1'[Inscrits]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR selectedBreakdown = SELECTEDVALUE('%RempSess_Breakdown PEB'[Bloque]) VAR SelectedCategory = SELECTEDVALUE('%RempSess_Categories PEB'[Categorie]) VAR SelectedTime = SELECTEDVALUE('03_Sessions1'[Type session]) RETURN SWITCH(SelectedTime, "Passé", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Perdues sessions maintenues", Perdues_Sessmaintenues, Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Perdues sessions maintenues", 0, Inscrits )), "Futur", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Disponibles", [%RempSes_PdispoF], Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Disponibles", 0, Inscrits ) )) The measure used %RempSes_PdispoF = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Témoin suppression]<>"X")) Some data if needed : https://www.dropbox.com/scl/fi/phx9ydfuizuu0liwrveh3/Sessions-Waterfall-chart.xlsx?dl=0&rlkey=gr68u6p5i1p9i8udzgc31vh3g
ItoDiaz
3 years ago Place DAX Commands and Tips
1.6KViews
0likes
3Comments
DAX Switch Formula Failing to Load in Waterfall Chart
Hello All, Looking for assistance on a DAX formula that is seriously limiting my ability to build a report. The report centres on a Waterfall chart that is fed by a SWITCH formula, to allow for multiple measures to show the variances between two datasets (image below). The measure that feeds the chart is below: NS Waterfall Value = SWITCH ( VALUES ( 'NS Account Table'[Index] ), 1, [Vol/Mix Net Sales]/1000, 2, [Rate xFX Gross Sales]/1000, 3, [Rate xFX Promo Allowance]/1000, 4, [Rate xFX Other Trade]/1000, 5, [Rate xFX Slotting]/1000, 6, [Rate xFX Coupons]/1000, 7, [Rate xFX Cash Disc. and Unsaleable]/1000, BLANK()) The above is set up in a manner, in that an unconnected table is used as the category option for the waterfall chart, and then used in a switch formula to then sub in the individual measures into the points of the waterfall chart below. All measures are set up in the same way, with an example below: Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="Customer Sales")+Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="CPU Allowance") The Rate xFX measure being used, is another measure however that runs in 700-1000 ms, thus I have assumed this is not the issue. Stand alone with no filters, the above switch function can run up to 15 seconds, and when filtering for different characteristics, that can run up to a full minute, or even crash with the message stating "Not enough memory to complete this operation". Any help would be much appreciated, and feel free to let me know if any other data is needed.
Anonymous
4 years ago Place DAX Commands and Tips
526Views
0likes
1Comment
How to fill each row of a measure based on a single row value? (DAX Measure for Waterfall Chart)
I have a modified data table as seen below for a Waterfall visual. This table is set-up to have an Attributes column that contains labels, and Values column that contains corresponding numeric values. Italicized columns for ValuePaid, TotalPaidTime and ValueTotal are additional measures that I have created. LineSK ValuePaid TotalPaidTime Attribute Value ValueTotal 1 Non Operating Time 1220 13.93% 1 7540 Total Paid Time 7540 86.07% 1 14 Maintenance 14 1 138 Changeover 138 1 160 Clean / Sanitation 160 1 947.18 Other 947.18 1 6280.82 USLE 6280.82 The table is meant to power two separate Waterfall charts. As such, I have created 2 measures as follows: ValueTotal = CALCULATE(AVERAGEX('Waterfall Table','Waterfall Table'[Value] / (365*24)), FILTER('Waterfall Table', 'Waterfall Table'[Attribute] = "Total Paid Time" || 'Waterfall Table'[Attribute] = "Non Operating Time")) ValuePaid = CALCULATE(AVERAGEX('Waterfall Table', 'Waterfall Table'[Value]), FILTER('Waterfall Table', 'Waterfall Table'[Attribute] <> "Total Paid Time" && 'Waterfall Table'[Attribute] <> "Non Operating Time")) As seen above, ValueTotal is calculated as [Value] / (365*24). The (365*24) is a static total that [Value] is divided by to convert the measure to be "% of grand total". The same is required for ValuePaid, but with a dynamic denominator of [Value] for [Attribute] = [Total Paid Time] for each LineSK. I tried creating a measure called TotalPaidTime = CALCULATE(AVERAGEX(FILTER('Waterfall Table','Waterfall Table'[Attribute] = "Total Paid Time"), 'Waterfall Table'[Value]) ). My thinking was that I could divide the [Value] in ValuePaid with this measure. As seen from the sample data, this measure only populates the row where attribute itself is Total Paid Time, and not across all rows. I am therefore unable to divide the [Value] for measure ValuePaid by TotalPaidTime to get "% of grand total%", as doing so results in division by blank cells. How can I set-up a measure where each row of ValuePaid is divided by the TotalPaidTime i.e., a table that looks like this - LineSK ValuePaid TotalPaidTime Attribute Value ValueTotal 527 7540 Non Operating Time 1220 13.93% 527 7540 Total Paid Time 7540 86.07% 527 0.19% 7540 Maintenance 14 527 1.83% 7540 Changeover 138 527 2.12% 7540 Clean / Sanitation 160 527 12.56% 7540 Other 947.18 527 83.30% 7540 USLE 6280.82
Solved
dhkhanna
4 years ago Place DAX Commands and Tips
1.5KViews
0likes
1Comment
How to find cumulative frequency by likert scale category and date and by using date drill down.
Hi, I am new to Power BI. Pls guide me with the problem below: I have survey data which is coming every day. I am using likert scale (1, 2, 3, 4, 5) and wanted to do cumulative sum or frequency count for each likert category (1, 2, 3, 4, 5) with respect to date e.g: if I drill down to month it should give me cumulative sum of each category of the data in that specific month along with its total no. of data set in that month when I use waterfall chart. My sample data set is given below. I made second table to find the sum of each category by using group by specifiying Overall learning experience column and applying count rows operation. This provided me new table with the total count of each category but not by month, date. When I drill down to month the sum of each category are mixed and end total responses exeeds. Total no of responses are 479. Please see the pictures Correct Total sum of each category per month is ruined as well total no. of responses also get wrong as correct value is 479 Total sum of each category per day is ruined as well total no. of responses also get wrong as correct value is 479 Please guide me how to solve this issue. I wanted to see total no of responces of each category along with its independent total overall, by month, day, so on. Also I want to make separate chart for only 1 and 2 rating by using DAX or what ever!!! Regards. ID Start time Completion time Email Name How was slides sharing quality How was sound quality How was the camera quality Overall learning experience Any suggestions or issue you are facing? Campus Overall motivation 1 3/22/20 14:37:01 3/22/20 14:38:52 [email protected] XYZ 5 5 5 5 This is my first time to experience online classes which is very great if I see it as technology perspective. But the trainer's lecture was interesting which makes me engaged to the lecture. I really liked the online classes concept. California 5 2 22/03/2020 14:44 22/03/2020 14:44 [email protected] XYZ 5 5 5 5 No Sydney 4 3 22/03/2020 14:48 22/03/2020 14:49 [email protected] XYZ 4 3 3 4 Good working Dubai 4
Solved
HassanMoosa
6 years ago Place DAX Commands and Tips
1.7KViews
0likes
2Comments
How to do cummilative sum of likert scale category by date.
Hi, I am trying to do sum of likert scale category (1, 2, 3, 4, 5) by date drill down. Like when I drill down to month it should just show the cumulative sum of each category by that month total no of responses and so on. But now it is just showing total sum of categories but when drill down to month and day its sum runies. I made new table and grouped it by "Overall learning experience" and applied count rows operation but its not working when Drill down to month and day. Please guide me how to group likert scale data and find cumulative sum of each category (1, 2, 3, 4, 5) and total responses by each month and days by using DAX or what so ever. The pictures of the charts are attached below: In picture 1 all good. In picture 2 its showing wrong cumulative sum by month I want cumulative sum by each month e.g: March 73, April 393, May 13 thus cumulative should be 479. Similarly in picture 3 it shows cumulative sum in thousands. It should again be as per days. And sync to all other visuals. Please see my data below. ID Start time Completion time Email Name How was slides sharing quality How was sound quality How was the camera quality Overall learning experience Any suggestions or issue you are facing? Campus Overall motivation 1 3/22/20 14:37:01 3/22/20 14:38:52 XYZ@XYZ XYZ 5 5 5 5 This is my first time to experience online classes which is very great if I see it as technology perspective. But the trainer's lecture was interesting which makes me engaged to the lecture. I really liked the online classes concept. XYZ 5 2 22/03/2020 14:44 22/03/2020 14:44 XYZ@XYZ XYZ 5 5 5 5 No XYZ 2 3 3 22/03/2020 14:48 22/03/2020 14:49 XYZ@XYZ XYZ 4 3 3 4 Good working XYZ 3 2
Solved
HassanMoosa
6 years ago Place DAX Commands and Tips
3.2KViews
0likes
6Comments
Waterfall chart YOY YTD
Hi, I created a waterfall chart associated with a period slicer. I want to compare data YTD for 2018 and YTD for 2019. As you can see it takes full year turnover for 2018. How can I compare YTD 2018 and YTD 2019 with a DAX measure? Here : from 2018-01-01 to 2018-01-03 and 2019-01-01 to 2019-01-03 Thanks
Anonymous
6 years ago Place DAX Commands and Tips
1.8KViews
0likes
3Comments