A Table of multiple was supplied where a single values are expected

[Prabhakaran_Nag]

Hello Everyone, Good evening. I am facing an issue with the Formula or Measure. Need your help to support on requirements which is not met in PowerBI Desktop.

Scenario

1. Need to define targets based upon the employee details by Locations but system giving an error. Formula which i have used : External Certification Target 10% = IF(VALUES('NGGM Base'[ATC])="ATCP",COUNTA('NGGM Base'[Acc Email Id])*.05,COUNTA('NGGM Base'[Acc Email Id])*.10)

If the location set as top filter is ATCP it should pick 5% of HC, i have used above formula it was working in column chart and pie chart but not in table. Given screenshot below

File in table view looks like this. Kindly advise on prioirty as i have some deliverables tomorrow

[v-zhouwen-msft · Accepted solution]

Hi @Prabhakaran_Nag ,

I tested the dax expression you provided and it gave the same error, I think there is a problem with dax itself, I tried to rewrite the dax expression.

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = IF(SELECTEDVALUE('NGGM Base'[ATC]) = "ATCP",
COUNTAX(FILTER(ALL('NGGM Base'),'NGGM Base'[ATC]  = "ATCP"),'NGGM Base'[ACC Email Id]) * 0.05,
COUNTAX(FILTER(ALL('NGGM Base'),'NGGM Base'[ATC] = SELECTEDVALUE('NGGM Base'[ATC])),'NGGM Base'[ACC Email Id]) * 0.1)

2. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Prabhakaran_Nag · Accepted solution]

Thanks Wenbin Zhou, sorry away and didnt respond on time. Yes, it is picking the data but one more scenario appeared not only based on ATC column it also by the sub parameter which is CG. 

Example: Comms & Media Headcount 4244 but the value given for ATC only. Actually it should be 10% of HC i.e., for C&M it should be 424 but overall it shown as 1908 based on "ATCI". it should down drill to other values as 75%. Kindly help on the same.

[v-zhouwen-msft · Accepted solution]

Hi @Prabhakaran_Nag ,

I tested the dax expression you provided and it gave the same error, I think there is a problem with dax itself, I tried to rewrite the dax expression.

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = IF(SELECTEDVALUE('NGGM Base'[ATC]) = "ATCP",
COUNTAX(FILTER(ALL('NGGM Base'),'NGGM Base'[ATC]  = "ATCP"),'NGGM Base'[ACC Email Id]) * 0.05,
COUNTAX(FILTER(ALL('NGGM Base'),'NGGM Base'[ATC] = SELECTEDVALUE('NGGM Base'[ATC])),'NGGM Base'[ACC Email Id]) * 0.1)

2. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.