BI Matrix with Count of Blank cells per column and grouped by first column.

[sunny2225]

Hi!

Ive spent a few hours looking through similar posts but cant find a solution that's relevant to my issue.

I am trying to create a Matrix visual to show the percentage of blank cells in each column in a very large data set that needs to be grouped by city (SITE_CODE).

Here is a sample dataset. The actual dataset has over 250k rows and over 50 columns but is setup similarly with SITE_CODE containing no null values.

SITE_CODE	INVOICE_ID	MARKET	CUST_ID	ORDER_ID	Product
12		x
11	INV	x		gdcc2323
12	INV	x	123321		pen
13	INV
13	INV	xy
12	INV	xy			paper
13	INV	xy	123322
13	INV
12	INV	x		ijs1172
11					paper

I have tried to unpivot the data and use the countblanks() formula but that doesn't work and I run into a resource limit issue. Similarly the " is blank " filter doesn't seem to be working at all when i create a matrix off the original dataset. Ideally I would like the resulting dataset to look something like this. 

SITE_CODE - >>>	11	12	13	14	15
INVOICE_ID	%	%	%	%	%
MARKET	%	%	%	%	%
CUST_ID	%	%	%	%	%
ORDER_ID	%	%	%	%	%
Product	%	%	%	%	%

Any advice or help would be appreciated. In the future I would like to also be able to add the ability to calculate these percentages based on a specific error value to aid in this data cleansing process.

Thanks!

[lbendlin · Accepted solution]

So you are trying to re-invent the "Column Statistics"  wheel?