Error in creating a measure using SUMX with AND/&&

[evanyne09]

Hello all, I'm trying to create a measure with deeper calculation but am getting error on it. Could someone help me with this, not sure what went wrong?

Final% = IF(ISFILTERED('Sheet'[A]) || ISFILTERED('Sheet'[B]),

SUMX('Sheet',

('Sheet'[Total Value]*ISFILTERED('Sheet'[A]))

&& ('Sheet'[Total Value]*ISFILTERED('Sheet'[B])))

/ IF(ISFILTERED('Sheet'[A]) || ISFILTERED('Sheet'[B]),

SUMX('Sheet',

ABS(

('Sheet'[Total Value]*ISFILTERED('Sheet'[A]))

&& ('Sheet'[Total Value]*ISFILTERED('Sheet'[B]))))))

*100

[FreemanZ]

hi @evanyne09 

what do you expect out of such code?

[FreemanZ]

hi @evanyne09 

to avoid any misunderstanding, could you post some sample data with expected result?

[evanyne09]

Hi @FreemanZ , here's the example of a data set where I am trying to evaluate the completeness of a user's registration of interest and his historical records in a company's database, to gauge the likelihood of onboarding this user to a product or service.

(Somehow having issues uploading a table here, hence typing it out by column and row)

Column 1: Classification

Account History

Account History

Github Profile

Github Profile

Facebook Profile

Facebook Profile

LinkedIn Profile

LinkedIn Profile

Column 2: External Record/Completeness of Registration Form

-

-

User provided Github Profile

User did not provide Github Profile

User provided Facebook Profile

User did not provide Facebook Profile

User provided LinkedIn Profile

User did not provide LinkedIn Profile

Column 3: Internal Record

Clean Account History

Previously Blacklisted Account

-

-

-

-

-

-

Column 4: Weighted Score

4

-4

2

-2

2

-2

2

-2

In each of the classifications, there can only be 1 option selected.

Points are assigned to each of the items selected ("Weighted Score").

Let's say those in red are the ones that are selected.

Interpretation:

• Given that the user has a clean record with the company, I am inclined (+4 points) to onboard him.
• Given that the user has provided Github and Facebook Profile URL, I am inclined (+2 points each) to onboard him.
• However, this user did not provide his LinkedIn Profile URL at this point, which then reduces my likelihood of onboarding him (-2 points).

Based on this, I want to tabulate his score to get a percentage of my overall likelihood of onboarding him to the company's service/product.

Numerator: User's score based on the weighted point system

Denominator: Total score of all items that were selected.

[(4 points x 1 field selected from Column 1) + (2 points x 2 fields selected from Column 2) + (-2 points x 1 field)] / absolute figure of total score of all selected items * 100%, which is:

[(4 points x 1) + (2 points x 2) + (-2 points x 1)] / (4+2+2+2) * 100% = 60%.

This would mean I am 60% likely to onboard this user to the company's product/service.

Hope this helps in understanding what I am attempting to calculate!

Thanks in advance.