Comparison of results over years and outstanding entries

[nightingale03]

Hey All,

I am trying to replicate a report into Power BI.

I have a Full Client list, which has a many-to-one relationship with the survey result table.

These surveys are conducted 1st per quarter (3 month period).

The results are reported monthly, against that period.

I have the result count part reasonablitly solved, the bit i can't seem to create a measure of is how many results are "outstanding". ie no related entry in the right table.

the desired result would be a matrix table, with the counts of the results:

Client Name || Bad Fail || Fail || Pass || Excellent Pass || Outstanding

Any help would be appreciated.

[v-huizhn-msft]

Hi @nightingale03,

I try to reproduce your scenario, but it's really hard to do it without your sample table. Could you please share your sample table and expected result, so that we can post solution which is close to your requirement.

Best Regards,
Angelia

[nightingale03]

Thanks for your reply, i have uploaded simple example dataset to here

[v-huizhn-msft]

Hi @nightingale03,

The link is invalid, please update.

Thanks,
Angelia

[nightingale03]

TBL_Customer
Customer ID	Customer Name	Area
1	Oxford	Blue
2	Cambridge	Blue
3	Bristol	Blue
4	London	Yellow
5	Glasgow	Yellow
6	Milton Keynes	Purple
7	Bedford	Red
8	Luton	Red

TBL_Results
NUMBER	TEXT	TBL_CUSTOMER.Customer ID	DATE	NUMBER	NUMBER	NUMBER	NUMBER
Result_ID	Result_Type	Customer_ID	Result_Date	Result_Outcome	Result_Period	Result_FQ	Result_FY
1	Full	1	01/01/2016	5	1	1	16
2	Full	1	15/01/2017	6	1	1	17
3	Full	2	03/01/2016	5	1	1	16
4	Full	3	04/01/2016	4	1	1	16
5	Full	3	10/01/2017	5	1	1	17
6	Full	4	10/01/2016	4	1	1	16
7	Full	4	20/01/2017	3	1	1	17
8	Full	5	02/01/2017	6	1	1	17
9	Full	6	19/01/2017	4	1	1	17
10	Full	7	30/01/2016	5	1	1	16
11	Full	8	20/01/2017	2	1	1	17
12	Partial	8	21/01/2017	4	1	1	17

desired results:

RESULTS ARE FOR QUARTER (Report Filtered?)
		Count of Results	Any Customer without a result in Period	TOTAL = Total in tbl_customer
		0	1	2	3	4	5	6	Unclassified	Total
Blue	2016					1	2			3
	2017						1	1	1	3
Yellow	2016					1			1	2
	2017				1		1			2
Purple	2016								1	1
	2017					1				1
Red	2016						1		1	2
	2017			1		1				2

[v-huizhn-msft]

Hi @nightingale03,

Thanks for your detailed sharing. If there is relationship between the two tables? And what's the rule of calculating "Any Customer without a result in Period" and "TOTAL = Total in tbl_customer"?

Best Regards,
Angelia

[nightingale03]

Hi,

Customer_ID is the relationship.

correct on the total line, total number of customers.

Thanks,

Ben

[v-huizhn-msft]

Hi @nightingale03,

But I still how to calculate the "Any Customer without a result in Period" and "TOTAL = Total in tbl_customer", could you please give me an example?

Best Regards,
Angelia

[nightingale03]

That is the issue i am having, in a manual sense it would be 

Total Customers - Total Customer Results = Unclassified in Period

[v-huizhn-msft]

Hi @nightingale03,

I am still confusing your expected result. Could you explain how to get the "Count of result"(column highlighted in red line), "Any customer without a result in Period"(column highlighted in black line)? There are extra several columns(part highlighted in blue line), what the meaning and how to get them? The most strangely, there is an extra row(highlighted in yellow backgroud)? Please please share more details, so that we can give solid solution.



Best Regards,
Angelia

[nightingale03]

Hi,

I think it is just formatting which is confusing here..

the results can be (the start of your yellow highlighting):

• 0
• 1
• 2
• 3
• 4
• 5
• 6
• unclassified

if the customer in the tbl_customer doesn't have a result for that quarter, then it goes to an unclassified result.

the Total row should equal the count of customers. therefore the unclassified is the balancing figure:

Customers - Results in Quarter = unclassified results 

[v-huizhn-msft]

Hi @nightingale03,



Which means in your result matirx, you need have the 8 columns(0,1,2,3,4,5,6,unclassified)? How to judge if the customer in the tbl_customer doesn't have a result for that quarter, please give an example.

Best Regards,
Angelia

[nightingale03]

in SQL terms, if you perform a LEFT join on tblcustomer and tblresult (where quarter = 1 for example) and the result is:

Customer Name | Customer Number | Result | Result Date

Blue                    |  1                            |  NULL  | NULL