Assistance with Waterfall Visualization

[Sulsa]

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 Policy Status by policy and a "Policy Net" (+1,0,-1) , so policy only occurs once per month.

Policies can be:

Transaction Type	Net
Endorsement	0
NonRenewal	-1
New Business	1
Renewal	0
Reinstated	1
Cancelled	-1

I'd like to make the chart dynamic so that the data can be filtered by a date dimension- which I have working. However I'd also like to:

1. Exclude Policies (red below) in the same date range that net  (Blue below)to zero (and everything that is already a zero). ie: if a policy is new business(+1) and a cancel(-1). I'd like to exclude it
2. Include the first Policy transaction type if it is positive. ie: if a policy is  new business(+1) , a cancel(-1), then a reinstatement (+1) Id like to only include the new business
3. Include the last Policy transaction type is negative. ie: if the policy was already a renewal (0), then a cancel (-1) then a reinstatement (+1), then a nonrenewal (-1). I'd like to have it be counted as only a nonrenewal. (if this is difficult, it can have the same rule as #2)
4. Retain other filters (if possible)

I am happy to provide any additional information. I can make structure changes if needed.

Thank you for your assistance

- Aaron

[v-zhenbw-msft · Accepted solution]

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]

Hi @Sulsa ,

Do you want to show the only one Net policy of each policy based on the Date?

For example, 2018/11/30 – 2018/12/31, the Net policy is 2, 1 plus 1.

In 2018/11/30 – 2019/1/31, the Net policy is -2, -1 plus -1.

Or is your issue similar to Open and Close ticket?

If it doesn’t meet your requirement, could you please provide a mockup sample and expected result based on fake data in a pbix file?

It is fine that the sample data is similar to your previous screenshot.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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.

[Sulsa]

Hello @v-zhenbw-msft 

I appologize as I thought this reply went out yesterday, but I guess there were some issues I didn't see until today.

Thanks again for your help on this. That looks like a brilliant solution, however the partitioning for policy number is not considered.  I would like to attach an example, but I can't figure out how to remove credentials from our database.

I have the start and end numbers working fine, it is the in between steps that are causing me issues.  Here are my measures for Start and End counts - note I'm using  the datavitz waterfall chart, which allows me to add a dedicated start and end measure :

Start Count = calculate(COUNTA([PolicyKeyStart]),filter('Fact Policy Transactions','Fact Policy Transactions'[AsOfDateKey]=MIN('Dim As Of Date'[DateKey])))


End Count = calculate( counta([PolicyKeyEnd]),filter('Fact Policy Transactions','Fact Policy Transactions'[AsOfDateKey]=MAX('Dim As Of Date'[DateKey])))

 I do not have to use that vizualization, but the fact that Start and End counts are seporated, it made it easier for me. I can see how your solution utilizes the included waterfall chart, which will save us money.

not sure if this will help, but I'm trying to get fact table that filters dynamically with the date dimensions and where the transaction type by  and net by policy where : 

rank() over (partition by policy order by datekey asc) rnk where rnk=1 and   sum(net policies) over (partition by policy) =1

unioned with

rank() over (partition by policy order by datekey desc) rnk where rnk=1 and sum(net policies) over (partition by policy) =1 =-1

Here is my fact table:

Dimensions are in the previous post.

Thanks again for helping out. I'd be happy to provide additional clarification.

Best regards

[v-zhenbw-msft]

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.

[v-zhenbw-msft]

Hi @Sulsa ,

Sorry for that we are not clear about your issue.

Do you want to show the status of each month?

For example, in January, we have new business and cancelled, but the January column will show 1.

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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.

[Sulsa]

Hello @v-zhenbw-msft 

It is a bit confusing, I appologize for being unclear. I'll do my best to convey the issue. I really appreciate your assistance as I've been working hard on this and not getting anywhere.  I was trying to make some measures with variables trying to get the first and last transaction over a partion (partitioned by policy). Needless to say it is not working well.

I believe the only relevant info is :

• 'Dim as of Date'[Date]
• 'Dim as of Date'[DateKey]
• 'Fact Policy Transactions'[AsofDateKey]
• 'Fact Policy Transactions'[Policy]
• 'Fact Policy Transactions'[Net Policies]
• 'Dim Transaction Type'[Transaction Name]

However I'd like to be able to also filter by the other dimensions (Like Product or Territory).

This is working sofar except I would like to not include anything that nets to 0 using the transaction type above. Note I only have transactions as of the End Of Month.

Note I do not care about the zero's (Renewals/endorsements)

If the date filter is 11/30/2018-12/31/2019 I would want to capture the new business transaction in the transaction count and the waterfall chart as +1

if the filter was from 12/31/2018-1/31/2019 I would want to capture the cancellation in the transaction count and the waterfall as a -1

if the filter was from 1/1/2019-1/1/2020, I would like for this to be excluded totally as This would be included in the "Starting Value" of the waterfall chart and nets to zero between the slicer dates. Same for if it the slicer date was between 11/31/2018- 1/31/2019 as the net =0.

If the Filter was from 11/30/2018-2/28/2019, I'd like to include only the New Business Transaction (+1) as all the transactions (New Business [+1] -Cancel [-1] + Re-instatement[+1] nets to +1 and the New business transaction is the first transaction

if the filter was from 1/31/2019-5/30/2019 - nets to -1, so I would prefer to only use the -1 from the last Transaction (but I would be ok using the first if this was difficult).

So if it sums to 0, exclude all transactions- I'll inlude them in the beginning and ending counts. 

if it sums to 1 - use the first transaction only

if it sums to -1 - use the last transaction only (but first would be acceptable)

Does that help at?  If not, I'd be happy to supply more information or sample data.

Thanks again!