Change of Measure based on slicer selection

[krrish116]

Hi @amitchandak ,

Please help me insorting out this...

I had a task :

i have 5 values (I&D, Olefins, Polyolefins, Refining, Technology) in a business segment column.

The business segments i have to show in a slicer.

for 5 values there are seperate measures

 and i get them all together in a single measure as below by using Variables.

Reliabilty % =

var Segment = SELECTEDVALUE('PlantArea'[Business Segment])

var IandD =CALCULATE([I&D Reliability%],'PlantArea'[Business Segment] = "I&D")

var Ole = CALCULATE([Olefins Reliability%],'PlantArea'[Business Segment] = "Olefins")

var Poly = CALCULATE([Poly Reliabilty %],'PlantArea'[Business Segment] = "Polyolefins")

var Ref = CALCULATE([Refining Reliability%],'PlantArea'[Business Segment] = "Refining")

var Tech = CALCULATE([Technology Reliability%],'PlantArea'[Business Segment] = "Technology")

return

IF(Segment = "I&D", IandD,

IF(Segment = "Olefins", Ole,

IF(Segment = "Polyolefins", Poly,

IF(Segment = "Refining", Ref,

IF(Segment = "Technology", Tech)))))

I have kept the slicer as single select so now they can select any one and it is working fine.

Now the problem is they want multiselect and if they select 2 values then it will show average of the 2 measures selected.

Please let me know how to create average, if they select 2 values in business segment slicer.

Thanks,

Krish..

[Anonymous · Accepted solution]

It's weird because on my test sample it works just fine...

Try this measure:

Reliabilty % =

var tempTable=ADDCOLUMNS(VALUES('PlantArea'[Business Segment]),"_value"

SWITCH('PlantArea'[Business Segment];

"I&D", [I&D Reliability%],

"Olefins", [Olefins Reliability%],

"Polyolefins", [Poly Reliabilty %],

"Refining", [Refining Reliability%],

"Technology", [Technology Reliability%])

)

return

AVERAGEX(tempTable,[_value])

[Anonymous]

@krrish116 Try this:

Reliabilty % =

var IandD =CALCULATE([I&D Reliability%],'PlantArea'[Business Segment] = "I&D")

var Ole = CALCULATE([Olefins Reliability%],'PlantArea'[Business Segment] = "Olefins")

var Poly = CALCULATE([Poly Reliabilty %],'PlantArea'[Business Segment] = "Polyolefins")

var Ref = CALCULATE([Refining Reliability%],'PlantArea'[Business Segment] = "Refining")

var Tech = CALCULATE([Technology Reliability%],'PlantArea'[Business Segment] = "Technology")

var tempTable=ADDCOLUMNS(VALUES('PlantArea'[Business Segment]),"_value",

IF('PlantArea'[Business Segment]= "I&D", IandD,

IF('PlantArea'[Business Segment]= "Olefins", Ole,

IF('PlantArea'[Business Segment]= "Polyolefins", Poly,

IF('PlantArea'[Business Segment]= "Refining", Ref,

IF('PlantArea'[Business Segment]= "Technology", Tech)))))

)

return

AVERAGEX(tempTable,[_value])

[krrish116]

@Anonymous It is not working ..

Please help me to sortout this

[Anonymous]

How exactly it's not working? Some errors or anything?

[krrish116]

No Errors Sir as of now..

but if i select 2 values in slicer then it is coming blank. it is not showing Average.

Olefins Selected

Poly Olefins selected

If i select 2 then it is not showing Average it is showing blank

[Anonymous · Accepted solution]

It's weird because on my test sample it works just fine...

Try this measure:

Reliabilty % =

var tempTable=ADDCOLUMNS(VALUES('PlantArea'[Business Segment]),"_value"

SWITCH('PlantArea'[Business Segment];

"I&D", [I&D Reliability%],

"Olefins", [Olefins Reliability%],

"Polyolefins", [Poly Reliabilty %],

"Refining", [Refining Reliability%],

"Technology", [Technology Reliability%])

)

return

AVERAGEX(tempTable,[_value])

[krrish116 · Accepted solution]

Thanks alot it is working..

[amitchandak]

@krrish116 , if 'PlantArea'[Business Segment] joined with table having (or is the same tbale)

Then this should work

CALCULATE([I&D Reliability%])

I am really not sure on need of 5 meaures, you can try like this too.

CALCULATE([I&D Reliability%],filte('PlantArea', 'PlantArea'[Business Segment] in allselected('PlantArea'[Business Segment]) ) )

[krrish116]

For all 5 Business Segments the measures are different for some business segments im using 2 columns for some im using 3 columns to multiply and divide to get the %