Only show subtotal, not calculated measures

[Jriker1]

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]

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]

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]

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]

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]

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]

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

 

[Jriker1]

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]

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]

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.