Forum Discussion

Sulsa's avatar
Sulsa
Frequent Visitor
5 years ago
Solved

Assistance with Waterfall Visualization

Hello All, I am returning back to Power BI after a couple years of using an "other" BI solution.  (happy to be back). Still kicking off the rust. I have a fact table that has End of Month Pol...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Don't @Sulsa,

    If you have fixed the issue on your own, please share your solution. If the help of the above messages, please mark it as a solution to help others find it more quickly.

    Best regards

    Community support team _ zhenbw

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
5 years ago

Hi Sulsa ,

 

What is the logic of start count and end count?

Do you want to create a waterfall chart that contains start count and end count?

If yes, you can refer the following steps.

 

1. Create a net column in fact table.

 

Net = 
CALCULATE(SUM('Policy table'[Type Net]),FILTER('Policy table','Policy table'[Transaction]='Fact Table'[Transaction]))

 

 

2. Then we need to create two tables using Enter data.

 

 

 

3. We can create a measure to get the waterfall.

 

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Start and End'[Sort] ),
    1, SWITCH (
        SELECTEDVALUE ( 'Name Table'[Transaction] ),
        "Cancelled", -1 * CALCULATE(SUM('Fact Table'[Net]),FILTER('Fact Table','Fact Table'[Transaction]="Cancelled")),
        "New Business", -1 * CALCULATE(SUM('Fact Table'[Net]),FILTER('Fact Table','Fact Table'[Transaction]="New Business")),
        "NonRenewal", -1 * CALCULATE(SUM('Fact Table'[Net]),FILTER('Fact Table','Fact Table'[Transaction]="NonRenewal")),
        "Reinstated",-1 * CALCULATE(SUM('Fact Table'[Net]),FILTER('Fact Table','Fact Table'[Transaction]="Reinstated")),
        [Start count]
    ),
    2, SWITCH (
        SELECTEDVALUE ( 'Name Table'[Transaction] ),
        "Cancelled", 0,
        "New Business", 0,
        "NonRenewal", 0,
        "Reinstated",0,
        [End count]
    )
)

 

 

Note: we configure the start count as 100.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

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

 

BTW, pbix as attached.