Define value if multiple values are selected in Slicer

[Anonymous]

Hi everyone.

I've tried to search for the solution, but i've failed to find one.

I need to count penetration for each product in each district, which is defined by data_provider.

pos = point of sales,

pos_num_dp = total number of pos in district and data_provider,

pos_num = total number of pos in district

product_name	district	data_provider	pos_w_sales	pos_num_dp	pos_num
mango	central	main_distributor	181	330	551
mango	central	sub_distributor	69	221	551
avocado	central	main_distributor	299	330	551

Everything is fine when one value is selected in slicer "data provider". Problem appears, when I need to calculate it for both of them selected, because for avocado it still would be 330, but not 551.

So, what i need is - when both values are selected in slicer divide by pos_num, when single - pos_num_dp.

I tried with if(and(selectedvalue('table1'[data_provider]) = "main_distributor",selectedvalue('table1'[data_provider]) = "sub_distributor"),pos_num,pos_num_dp), it didn't work.

Help me please.

[Anonymous]

By the way, the reason your formula with SELECTEDVALUE didn't work is that this works like HASONEVALUE internally (cf. the documentation at https://docs.microsoft.com/en-us/selectedvalue-function: "An equivalent expression for SELECTEDVALUE(<columnName>, <alternateResult>) is IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)."). Therefore it returns FALSE if more than one value is selected, even if the value that you test for is one of the selected values.

[Anonymous]

You could try HASONEVALUE:

IF ( HASONEVALUE ( data_provider ), pos_num_dp, pos_num )