Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
You could try HASONEVALUE:
IF ( HASONEVALUE ( data_provider ), pos_num_dp, pos_num )
User | Count |
---|---|
90 | |
73 | |
68 | |
63 | |
56 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |