I have an excel spreadsheet that pulls in my customer details and the products I buy. I have a measure below that puts them into Income Groups. I would like to add a row in my grouping for customers who don’t buy the products I am looking at. These customers don’t therefore have a 0 value associated them it’s just they have no values at all!
This is to help me see how new product launches are going and who isn’t buying certain products.
My measure which works is:
Customer Sales by Sales Group £ =
(calculate ([Total £ Sold],
[Total £ Sold] > ‘Income Groups’[Min] && [Total £ Sold] <= ‘Income Groups’[Max] ) )
>0 ) )
and the table results are:
OK, I get it. I am sure this is doable, but I think it’s going to be a relatively heavy lift because counting blank rows is a fundamentally different calculation than the other iterations over your Income Groups table.
The approach that comes to mind is based on the solution @Nick_M developed to address the “Top N and Others” question:
I think the outline of the general approach would be:
1). Using your current measure, replicate the info in your matrix visual virtually as a table variable
Develop a measure to count your blank rows, probably using COUNTBLANK, or COUNTROWS(ALL) minus COUNTROWS(ALLNOBLANKROW)
Using ROW, build a row incorporating the info from 2) and matching the structure of your virtual table in 1).
As another variable in the same measure as 1), append the row created in 3) to the table in 1) using UNION
write the DAX to output the relevant info from your complete virtual table in 4) to your report visuals as the RETURN statement in the measure created in 1) and 4)
As I said, doable but not easy… (unless someone has figured out a way to iterate over blank rows in a parameter table that I’m unaware of).
@Nick_M’s solution above is well-commented, so should provide a nice adaptable framework if you decide this is a road you want to go down. And the forum is always here to help…
In thinking a bit more about this, what makes the approach above difficult is combining apples and oranges calculations into the same table/matrix visual. If you were willing to present the non-purchasers’ (i.e., blank rows) info in a separate visual, that probably cuts the degree of difficulty by a factor of ten.
Im happy for a new visual to show those customers who haven’t purchased, and I think that will be the only way to do it, as essentially there is no data to look. The ‘Blanks’ only appear after pulling into a table visual for instance.
There’s a lot you can do within measures re: counting and filtering on blanks. Here’s a good video that addresses some of the functions you’ll probably find useful for this, including COUNTBLANK, COUNTA and COUNTAX:
And this is a very good overview of how DAX handles blanks, nulls and zeros: