Forum Discussion

PS_78's avatar
PS_78
Helper II
2 years ago
Solved

Help with data preparation for Waterfall Report

Hi Team - I am working on generating waterfall charts in my Power BI report. I have a dataset with "Raw Data" and I need to create a calculated table, "Waterfall" with columns "Categories" and "Value" to use them in the charts. I can somehow manage it in "Power Query" but any guidance on how I can generate this table in DAX that would be great. Attaching herewith the sample file I generated.

 

Waterfall Sample.pbix

 

Thanks,

Phani

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PS_78 ,

     

    You can use the following DAX to create the Waterfall table.

     

    Waterfall table = DATATABLE (
    "Category", STRING,
    "Value", INTEGER,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 2, 1},
        {"New_Year", 9, 2},
        {"Closed_Year", -3, 3},
        {"Starting Value_Qrtr", 8, 1},
        {"New_Qrtr", 1, 2},
        {"Closed_Qrtr", -1, 3}
    }
)

     

    If I have misunderstood your needs, please provide your expected result with backend logic and special examples.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PS_78 ,

     

    You can create a table.

    Waterfall table = DATATABLE (
    "Category", STRING,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 1},
        {"New_Year",2},
        {"Closed_Year",3},
        {"Starting Value_Qrtr",1},
        {"New_Qrtr",2},
        {"Closed_Qrtr", 3}
    }
)

    Then you can create a column.

    Value = 
var _new_year=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())))
var _close_year=-CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Closed])=YEAR(TODAY())))
var _start_year_1=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())-1 && [Date Closed]<=DATE(YEAR(TODAY()),1,1)))
var _start_year_2=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())-1 && [Open/Closed]="Open"))
var _new_qrtr=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',QUARTER('Raw Data'[Date Identified])=QUARTER(TODAY())))
var _close_qrtr=-CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',QUARTER('Raw Data'[Date Closed])=QUARTER(TODAY())))
var _start_qrtr=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())  && [Date Closed]<=DATE(YEAR(TODAY()),QUARTER(TODAY())*3-2,1)))

RETURN SWITCH([Category],
"Starting Value_Year",_start_year_1+_start_year_2,
"New_Year",_new_year,
"Closed_Year",_close_year,
"Starting Value_Qrtr",_start_qrtr,
"New_Qrtr",_new_qrtr,
"Closed_Qrtr",_close_qrtr)

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PS_78 ,

     

    You can use the following DAX to create the Waterfall table.

     

    Waterfall table = DATATABLE (
    "Category", STRING,
    "Value", INTEGER,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 2, 1},
        {"New_Year", 9, 2},
        {"Closed_Year", -3, 3},
        {"Starting Value_Qrtr", 8, 1},
        {"New_Qrtr", 1, 2},
        {"Closed_Qrtr", -1, 3}
    }
)

     

    If I have misunderstood your needs, please provide your expected result with backend logic and special examples.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

    • PS_78's avatar
      PS_78
      Helper II
      2 years ago

      Hello Anonymous - Sorry for responding late. Thanks for your response. That looks like a good idea. However, you are hardcoding the values. I am okay to hardcode category and SORT but not the value. I need to consolidate the "Value" from "Raw Data" table.

       

      Thanks,

      Phani

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi PS_78 ,

         

        Can you explain in detail with an example how to get the values of table 2 from table 1? What is their logic?

         

        Best Regards,

        Neeko Tang