Variance Measure to exclude 0

[KimC2504]

Hi All,
I have created a Measure Variance =[CW]-[PW]

I have created a table as per below, I would like the table to exclude the zero variance and show bike and Skateboard only. Can this be done?

Model	CW	PW	Variance
Car	100	100	0
Bike	50	40	10
Boat	66	66	0
Skateboard	30	10	20
Plane	99	99	0
Rollars	5	5	0
Helicopter	35	35	0
Motorbike	20	20	0

 

[etaf]

you want to put the words Bike, skateboard only - OR do you want the same word as the Model
CAR, Boat, Plane, Rollars, Helicopter, Motorbike, in your example
if so

=IF(CW]-[PW]=0, CellID with the [Model] , CW]-[PW] )

 

[Finalfight40]

Is this what you are after? If you have access to the new FILTER function.

Book5
	A	B	C	D	E	F	G	H	I	J
1	Model	CW	PW	Variance			Model	CW	PW	Variance
2	Car	100	100	0			Bike	50	40	10
3	Bike	50	40	10			Skateboard	30	10	20
4	Boat	66	66	0
5	Skateboard	30	10	20
6	Plane	99	99	0
7	Rollars	5	5	0
8	Helicopter	35	35	0
9	Motorbike	20	20	0
Sheet1

Cell Formulas
Range	Formula
G2:J3	G2	=FILTER(A2:D9, D2:D9<>0)
Dynamic array formulas.

 

[KimC2504]

Is this what you are after? If you have access to the new FILTER function.

Book5
	A	B	C	D	E	F	G	H	I	J
1	Model	CW	PW	Variance			Model	CW	PW	Variance
2	Car	100	100	0			Bike	50	40	10
3	Bike	50	40	10			Skateboard	30	10	20
4	Boat	66	66	0
5	Skateboard	30	10	20
6	Plane	99	99	0
7	Rollars	5	5	0
8	Helicopter	35	35	0
9	Motorbike	20	20	0
Sheet1

Cell Formulas
Range	Formula
G2:J3	G2	=FILTER(A2:D9, D2:D9<>0)
Dynamic array formulas.

Hi FinalFight40,
Yes that is what I want but that looks like an excel formula and I am working in Power BI.

 

[KimC2504]

you want to put the words Bike, skateboard only - OR do you want the same word as the Model
CAR, Boat, Plane, Rollars, Helicopter, Motorbike, in your example
if so

=IF(CW]-[PW]=0, CellID with the [Model] , CW]-[PW] )

Hi Etaf,

I would like my table to look like below. I have tried the if measure, but I couldn't get it to work

Model	CW	PW	Variance
Bike	50	40	10
Skateboard	30	10	20

 

[Finalfight40]

Hi FinalFight40,
Yes that is what I want but that looks like an excel formula and I am working in Power BI.

Ah my fault I didn't notice that.

 

[KimC2504]

I have worked it out. I can use a visual level filter.

Thanks all for your help