Show records in pivot table based on condition

[#1]

Hi there

I am trying to find sales transactions that show TVs sold but with NO TV license.  I just have 1 sales table in my model showing sales of TVs and TV licenses (indicated by SaleType).  I tried this using a straight table, see below, but then discovered that there was in fact a TV license sold, but it shows on a different line which doesn’t really help me.  So the ‘No TV Lic’ shown below in the calculated dimension would be wrong.

I then used a pivot table showing sales of TVs and TV licenses per sales doc (Dimensions are SalesDoc and SaleType).

3 questions:

• Is there a better way of showing this apart from the pivot table below?

• If not, how do I show only Sales docs where there was a TV sold but no TV license? I.e. where TV Article = 1 and TV License is null.

• And then how would I show the count of this and percentage that this count is of total TVs sold?

[Gysbert_Wassenaar]

I don't think you should use a calculated dimension. Instead change your chart expressions so they only calculate results for SalesDoc values that do not have a tv license:

count({<SalesDoc=E({<TVLicenseNumber={'*'}>}SalesDoc)>} distinct SalesDoc)