Slicer to Suppress Zeroes

[itsmk86]

We have a Product table with 2 Columns ProductName(Text) and ProductCost(Decimal). ProductCost includes 0 and non-zero values.

User wants a slicer to suppress/Unsuppress Zeros

Ex - Slicer named 'Show Zeros'- When selected 'Yes' should display ALL values (Zero and Non-Zero) for ProductName and ProductCost.

When selected 'No' should display ProductName and ProductCost with NO zeros.

SampleData -

         COLUMN 1

ProductName(Text)
Adjustable Race
Bearing Ball
BB Ball BearingHeadset Ball Bearings
Blade
LL Crankarm
ML Crankarm
HL Crankarm
Chainring Bolts
Chainring Nut
Chainring
Crown Race
Chain Stays
Decal 1
Decal 2
Down Tube

          COLUMN 2

ProductCost(Decimal)
11.11
0
0.01
0.32
0.32
0.32
0
0.32
4.8
5.8
0
2.1
6.9
0
11.23
0

Thanks! 

[camargos88]

Hi @itsmk86 ,

Check this file:  Download PBIX 

The point is to have a disconected table for YES/NO and this measure:

_Cost =

VAR _slicer = SELECTEDVALUE('Show Zeros'[Show Zeros])

RETURN IF(ISFILTERED('Show Zeros'[Show Zeros]); IF(_slicer = "Yes";

SUM('Table'[ProductCost(Decimal)]);

CALCULATE(SUM('Table'[ProductCost(Decimal)]); 'Table'[ProductCost(Decimal)] = 0)); SUM('Table'[ProductCost(Decimal)]))

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[itsmk86]

@camargos88 

Thanks for the reply. 

When I click on Slicer 'Show Zeros' - No , it displays Only Zeros but not non-zeros ? The requirement is to display Zeros when Selected 'Yes' (Something works with your logic) but when Slicer is left unselected or when selecting 'No', it should exclude the values containing zero and still display the remaining values with Zero.

Ex - When Slicer left unselected or selecting No , it should display Adjustable Race, BB Ball bearing Headset Ball bearing, Blade , chain etc (as they dont have zeros) but not the Bearing ball , Crown race (as they have Zeros)

[camargos88 · Accepted solution]

@itsmk86 ,

It just needed an adjustment:

_Cost =

VAR _slicer = SELECTEDVALUE('Show Zeros'[Show Zeros])

RETURN IF(ISFILTERED('Show Zeros'[Show Zeros]); IF(_slicer = "Yes";

SUM('Table'[ProductCost(Decimal)]);

CALCULATE(SUM('Table'[ProductCost(Decimal)]); 'Table'[ProductCost(Decimal)] > 0)); SUM('Table'[ProductCost(Decimal)]))

I've updated the file.

Ricardo

[itsmk86]

@camargos88 

I get Syntax errors... Tried modifying but it wouldn't take it... 

[camargos88]

@itsmk86 ,

It works here, try changing the semicolon to coma.

Ricardo

[itsmk86]

@camargos88 

That Worked !! When removing commas it works. Thank you!

However, I had skipped the ProductQty (3rd Column) for ease of understanding in the Post and when I add ProductName, ProductQty and _cost(newlycreated), it doesn't work, probably because the ProductQty is not 0 in case where ProductCost is 0. 

It works when I remove ProductQty, any quick tips here ? 

I have already accepted it as solution, highly appreicate your help. 

Note - ProductQty has has decimal values

ProductQty

1
21
22
45
0.5
1
0
1
21
1
2
3
1
1
0
1

Thanks!!

[camargos88]

Hi @itsmk86 ,

Try changing the aggregation mode to not aggregate. 

Or you can create this measure:

_Qty =

VAR _slicer = SELECTEDVALUE('Show Zeros'[Show Zeros])

RETURN

IF(ISFILTERED('Show Zeros'[Show Zeros]);

IF(_slicer = "Yes";

SUM('Table'[ProductQty]);

CALCULATE(SUM('Table'[ProductQty]); 'Table'[ProductCost(Decimal)] > 0)

);

SUM('Table'[ProductQty])

)

Ricardo

[DataZoe]

@itsmk86 if you are looking for something like this:

you can do that by

1. creating a table (enter data) -- say Zero Toggle, and create a column, Toggle, with the values you want in the toggle.

2. create a measure for the prices called with zeroes

3. create a measure for the prices that converts zeroes to blanks

4. create a measure to switch them out based on the toggle

5. use the measure in step 4 in your table

6. create the slicer based on the Toggle column

Measure with the Zeros = sum(Zeroes[ProductCost(Decimal)])

Measure without the zeroes = if([Measure with the Zeros]=0,blank(),[Measure with the Zeros]) 

Measure = switch(SELECTEDVALUE('Zero Toggle'[Toggle]),
"With Zeroes",[Measure with the Zeros],
[Measure without the zeroes])

[itsmk86]

@DataZoe 

Thank You , The solution your provided likely works BUT I had started with the other post , hence marking that as solution. 

Thank you for posting. 

[DataZoe]

All good @itsmk86 , glad you were able to get it sorted! 🙂