Re: Cancellation from one company and rewritten wi...

Anonymous · ‎07-21-2022

Hi I really hope someone can help me here.

I have to track the numbers for when a client cancels with a certain company but rewrites with another.

Basically i need a measure that shows how much premium was rewritten with a different company.

I would be using this in a matrix to show for example one company had $400,000 in cancellations but A rewrote ($100,000) of that.

Ex)

A B C

Rewritten Premium

Cancelled Premium

A ($400,000) $50,000 $150.000

B ($250,000) $25,000

C ($876,000) $155,000 $55,000

Thank you!

lbendlin · ‎07-22-2022

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Anonymous · ‎07-26-2022

Thank you so much for your reply, however it may have been easier if i was able to provide you with my data model. The measures don't seem to work when incorporating the different tables. The sample table i gave you was columns from different tables not one.

Below is my data model.

Thank you!

Anonymous · ‎07-25-2022

Hi @lbendlin ,

I am not able to provide the exact data becasue that is confidental but i have made two seperate tables that replicate the data and the outcome needed.

This first table is what the inital data would look like. There are cancellations and rewrites from certain companies. Some are cancelled and rewritted with the same company, some are with different companies and some are just cancelled. I need to make a matrix that allows one to see how many cancellations are done with one company and if some of those cancelled files are rewritten, which companies are those.

Name	Cancellations	Rewrites	Insurance Companies
John	-$4,923		A
Jill		$2,438	A
Jill	-$1,820		A
Bob	-$5,176		C
Mark	-$2,364		D
George	-$738		C
Bill	-$30		D
Heather	-$1,250		F
Becca		$1,678	A
Becca	-$1,828		C
Brad		$204	A
Brad	-$400		D
Mitch	-$4,399		A
Saddie	-$406		A
Rick		$791	A
Rick	-$1,240		B
Chad		$3,640	C
Chad	-$3,476		A
Alex	-$2,472		F

Here is the matrix that is needed. If we look at company A, $-15,024 was the total for cancellations, however, A was able to retain $2438, while company C was able to rewrite $3640. Company F had a total of $-3722 in cancellations and none of the accounts that cancelled came back to either of the 6 companies.

Companies		Rewrites	A	B	C	D	E	F
	Cancellations
A	-15,024		2438		3640
B	-1240		791
C	-7742		1678
D	-2794		204
E
F	-3722

I really appreciate the help as i have tried everything. If more explanation is needed, please let me know!

Thank you

lbendlin · ‎07-25-2022

see attached pbix

Anonymous · ‎07-26-2022

Nvm I actually figured it out from your solution! Is there a way to now filter by fiscal year?

Thank you

Anonymous · ‎07-29-2022

Hi @lbendlin ,

Is there a way to filter the table by the fiscal year? I need to be able to flip through the different years and show the differences.

thank you

lbendlin · ‎07-29-2022

Your data model has the Fiscal year in the Calendar table. Put that field in the page or report filters, and use it as needed.

Anonymous · ‎07-29-2022

I've tired this but it doesnt change any of the numbers. Does there need to be a relationship between my tables? The table that you put into power bi is a summarized table on my end

lbendlin · ‎07-29-2022

I had assumed that you had shown your data model in the previous post. In that picture tables are properly related. Any filter can only affect related tables that are downstream from it (as indicated by the connection arrows)