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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jahnabiAP
Frequent Visitor

Want to Show YTD vs YTD Previous year in Waterfall

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

Waterfal.PNG

 

 

 

1 ACCEPTED SOLUTION

Hi @jahnabiAP ,

 

this is what our chart looks like:Waterfall created form stacked columns.png

 

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...

View solution in original post

6 REPLIES 6
V-lianl-msft
Community Support
Community Support

 
You can use the breakdown feature to solve this. 
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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:

 

  1.  Creating a custom table with our categories for the breakdown & a column to order them
  2. Creating a measure to calculate the deviation from each breakdown
  3. Creating a measure for each blank space "below" the deviation
  4. Use a stacked column chart

 

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:Waterfall created form stacked columns.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.