# Only show subtotal, not calculated measures



## Jriker1 (Feb 23, 2013)

In order to filter down my data to show a distinct list of textural values, I needed to create a numberic measure to show the results and eliminate blanks where data was not filled in.  Problem is the results are all the number "1" if they are valid to show on the resulting pivottable.  Of course the number itself has no value.  The measure is necessary to get the right results back.  I do however like the subtotal that shows the total number of items returned under it so:







I want to get rid of all those non bold 1's.  If I get the measure to return blank for the output, then like on Brazil, I get an extra row that is blank with a count of 3 instead of 2.  I can hide the whole Measure 5 column by selecting the column it resides in on Excel and hiding, but loose the subtotals.  Can live with that but prefer not to.

Measure 5 is: CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor])))

Table data is table name: Table_owssvr and data relevant here:


Country
Vendor
Application (App the Vendor worked on)


One line per application the vendor worked on so everything could be repeated multiple times in the table if a vendor did more than one app for the country or multiple versions of the app. Bunch more fields but not relevant.  Trying to show a distinct list of vendors each country has worked with, make sure blanks are skipped, and NOT show that stupid 1,1,1,1,1,1,1,1,1,1,1... for each line.  Is this possible at all?

Thanks.


----------



## miguel.escobar (Feb 24, 2013)

I believe that you could use an IF statement with ISFILTERED using the Vendor column and if it's filtered to return a blank and if is not then just run the measure that you already calculated....something like: =IF(ISFILTERED(Table_owssvr[Vendor]),BLANK(), CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]))))


----------



## Jriker1 (Feb 25, 2013)

Thanks for the reply.  I tried as you mentioned with some extra end brackets:
IF(ISFILTERED(Table_owssvr[Vendor]),BLANK(), (CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]))))))






So the counts are right at the country level, but the Vendor names dissapear even though they are listed in the Row Labels Field List area.

Thanks.


----------



## miguel.escobar (Feb 25, 2013)

If you want them to show you could change the BLANK() for 0 and the change the format number of that column so that 0's would be blank using a custom formatting.


----------



## miguel.escobar (Feb 25, 2013)

you can also use the FORMAT function inside the measure to change the values into blanks if you want

IF(ISFILTERED(Table_owssvr[Vendor]),Format(CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]), ";;;"), (CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]))))))


----------



## Jriker1 (Feb 25, 2013)

Thanks I'll see if I can get this to work.  I'm getting an expression is not valid error.  Think there are some missing end brackets somewhere.

Thanks.


----------



## miguel.escobar (Feb 25, 2013)

check this out
IF(ISFILTERED(Table_owssvr[Vendor]),FORMAT(0,";;;"), (CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]))))))


----------



## Jriker1 (Feb 25, 2013)

I get:  Calculation error in measure 'Table_owssvr'[b3e8242c-089d-4598-879e-61eb3cc449a9]: The second and third arguments of function IF have different data types. This is not supported.

Thanks.


----------



## miguel.escobar (Feb 25, 2013)

can you send me a sample of the workbook here miguel.escobar@poweredsolutionsonline.com ? I tried inserting a static number fo when the calculation was being filtered by the Vendor to return the #0 and then format that number so that it would be hidden...


----------



## miguel.escobar (Feb 25, 2013)

The 2nd approach that I mentioned can be found in this example that I created
https://dl.dropbox.com/u/54063091/Resolvedv2.xlsx

Thanks to the Custom Numeric format (Positive, Negative, Zero) we can make stuff dissapear like magicians! 





Please check the measure1. That's basically the most clean way that I could think of.


----------

