Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to use Waterfall visual for showing YTD VS YTD Previous Year, where the right total column will be Sales YTD FY20 the left total column will be YTD sales(for same period) for FY 2019 . I have 2 measures for both YTD. How to show in a waterfall.
The breakdowns will be various activities
Solved! Go to Solution.
Hi @jahnabiAP ,
this is what our chart looks like:
Even though the left bar does not show the previous year and the right one the actual (its a leadfunnle), this will work the same way for any deviation.
The red colums are simply a SUM Measure.
The measure for the blanks looks like this:
DAQBlank =
VAR __FilAbgAll = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbgeschlossenALL")
VAR __FilUngue = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketUngueltig")
VAR __FilGeloe = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketGeloescht")
VAR __FilErsch = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketErschienen")
VAR __FilAbbOPU = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchohneSchnupper-/PU")
RETURN
SWITCH(
VALUES(DAQ_FactSamsEnde[Attribut]);
"DAQTicketUngueltig";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbgAll
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilUngue
);
"DAQTicketGeloescht";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbgAll
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilUngue
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilGeloe
);
"DAQLeadsohneTermin";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketGueltig")
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQLeadsohneTermin")
);
"DAQTicketNoShow";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketLeads_mit_Termin")
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketNoShow")
);
"DAQTicketAbbruchohneSchnupper-/PU";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilErsch
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbbOPU
);
"DAQTicketAbbruchmitSchnupper-/PU";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilErsch
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbbOPU
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchmitSchnupper-/PU")
);
0
)
I am sure there is a much simplier way to fill the blanks, but that was what we came up with in the end...
@V-lianl-msft Thanks for you quick response.
But I am already using activities for breakdown.
So its like YTD changes over the activities for FY 20 VS FY 19.
I am using Fiscal year for categories, activities for breakdown, and Sales for Y axis values.
I would like tpo use YTD and Last year YTD in place of Y axis values.
Hi @jahnabiAP ,
we had a similar situation in a report. At the end we solved this by:
The measures were similar to this:
DEVIATION_BREAKDOWN =
SWITCH(
TRUE();
BREAKDOWN1;<Some calculation/Measure>;
BREAKDOWN2;<Some calculation/Measure>
)
BLANKS =
SWITCH(
TRUE();
BREAKDOWN1;YTD PREV YEAR +
CALCULATE(
<DEVIATION_BREAKDOWN>;
FILTER(
ALL(BREAKDOWN_TABLE);
BREAKDOWN == "BREAKDOWN1"
)
);
BREAKDOWN2;YTD PREV YEAR +
CALCULATE(
<DEVIATION_BREAKDOWN>;
FILTER(
ALL(BREAKDOWN_TABLE);
BREAKDOWN == "BREAKDOWN1" || BREAKDOWN == "BREAKDOWN2"
)
)
)
)
(Code written from head & not checked!)
Basically you need to add up the YTD Last Year and all the deviations for each breakdown(s) on and before the breakdown you are looking at.
We know that maintenance for this is pretty high, but it was the only way we could get it to work...
Chris
Thanks for the response @crisc .
But I want to show the YTD difference in a waterfall chart.
Can you show me how your chart will look like?
Hi @jahnabiAP ,
this is what our chart looks like:
Even though the left bar does not show the previous year and the right one the actual (its a leadfunnle), this will work the same way for any deviation.
The red colums are simply a SUM Measure.
The measure for the blanks looks like this:
DAQBlank =
VAR __FilAbgAll = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbgeschlossenALL")
VAR __FilUngue = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketUngueltig")
VAR __FilGeloe = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketGeloescht")
VAR __FilErsch = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketErschienen")
VAR __FilAbbOPU = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchohneSchnupper-/PU")
RETURN
SWITCH(
VALUES(DAQ_FactSamsEnde[Attribut]);
"DAQTicketUngueltig";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbgAll
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilUngue
);
"DAQTicketGeloescht";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbgAll
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilUngue
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilGeloe
);
"DAQLeadsohneTermin";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketGueltig")
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQLeadsohneTermin")
);
"DAQTicketNoShow";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketLeads_mit_Termin")
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketNoShow")
);
"DAQTicketAbbruchohneSchnupper-/PU";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilErsch
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbbOPU
);
"DAQTicketAbbruchmitSchnupper-/PU";
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilErsch
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
__FilAbbOPU
)
-
CALCULATE(
SUM(DAQ_FactSamsEnde[Wert]);
FILTER(
ALLSELECTED(DAQ_FactSamsEnde);
DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchmitSchnupper-/PU")
);
0
)
I am sure there is a much simplier way to fill the blanks, but that was what we came up with in the end...
Thanks very much for your solution.
Its a complex one though. 😛
I will try to use this approach.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |