Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Define value if multiple values are selected in Slicer

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_namedistrictdata_providerpos_w_salespos_num_dppos_num
mangocentralmain_distributor181330551
mangocentralsub_distributor69221551
avocadocentralmain_distributor299330551

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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You could try HASONEVALUE:

    IF ( HASONEVALUE ( data_provider ), pos_num_dp, pos_num )

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.