Simple 'IF' measure in Power Pivot (Error)

[powerwill]

Measure 1 SH-MET% = SUMX(VALUES(Table_Name[Agent_name]), IF([FINAL SCORE]>=0.95 && VALUES(Table_Name[Team])="Sales",1,0))

However, when I pull it in the Pivot, it shows the below Error. Any help is appreciated.

 

[Matt Allington]

VALUES() returns a table of values. In your case, you are using this table as the table parameter (the first parameter) inside SUMX. SUMX is an iterating function. It steps through every row of the table and performs the evaluation (the second parameter) for each row in the table (it’s more complex than this, but this is sufficient to understand in this case). By the time the second parameter inside SUMX is evaluated, there is only a single value for TABLE_Name[Team]. It was SUMX that selected the single value before doing each calculation through the iteration. But then you use VALUES again inside parameter 2. This has the effect of turning the single, scale value for this column back into a table. You can’t do a comparison between a table and a scalar value; that is what you are doing. The solution is simple. Because you are inside SUMX, you don’t need VALUES.

SH-MET% =
SUMX (
    VALUES ( Table_Name[Agent_name] ),
    IF (
        [FINAL SCORE] >= 0.95
            && Table_Name[Team] = "Sales",
        1,
        0
    )
)

 

[powerwill]

VALUES() returns a table of values. In your case, you are using this table as the table parameter (the first parameter) inside SUMX. SUMX is an iterating function. It steps through every row of the table and performs the evaluation (the second parameter) for each row in the table (it’s more complex than this, but this is sufficient to understand in this case). By the time the second parameter inside SUMX is evaluated, there is only a single value for TABLE_Name[Team]. It was SUMX that selected the single value before doing each calculation through the iteration. But then you use VALUES again inside parameter 2. This has the effect of turning the single, scale value for this column back into a table. You can’t do a comparison between a table and a scalar value; that is what you are doing. The solution is simple. Because you are inside SUMX, you don’t need VALUES.

SH-MET% =
SUMX (
    VALUES ( Table_Name[Agent_name] ),
    IF (
        [FINAL SCORE] >= 0.95
            && Table_Name[Team] = "Sales",
        1,
        0
    )
)

Thank you vmuch for your attention. I tried what you said, removed 'Values' from inside the 'IF' fx, doing that doesn't show me the column that I wish to refer, and if I manually type in the Column name, it throws this..because my Column 'Vertical/Team' has 5 team names that appear more than once because each team has 15+ people and I have multiple transactions under each person. 'Sales' is one of the team...not sure if that makes sense . and I would eventually want to Nest several '>=' conditions for each team, as their target would vary.

 

[powerwill]

deleted

 

[Matt Allington]

I believe my measure addresses the error you posted from your first post. If this doesn't solve your problem, please post what you are now trying to write along with the error

 

[powerwill]

I believe my measure addresses the error you posted from your first post. If this doesn't solve your problem, please post what you are now trying to write along with the error

=SUMX(VALUES(VALUE_CONSOL[Agentname]), IF([FINAL SCORE]>=0.95 && VALUE_CONSOL[Vertical]="Sales",1,0))

@Matt Allington

 

[Matt Allington]

My bad. What I posted only works if you have the same column in VALUES and inside SUMX.

Try this

=SUMX(VALUES(VALUE_CONSOL), IF([FINAL SCORE]>=0.95 && VALUE_CONSOL[Vertical]="Sales",1,0))

 

[powerwill]

My bad. What I posted only works if you have the same column in VALUES and inside SUMX.

Try this

=SUMX(VALUES(VALUE_CONSOL), IF([FINAL SCORE]>=0.95 && VALUE_CONSOL[Vertical]="Sales",1,0))

Tried this, but it shows the wrong %. I have attached a spreadsheet, if that helps.

I am trying get an overall (All teams) and Team wise % of Agents meeting their targets, as a Power Pivot measure.

In the attached sheet I have added one measure, for 'Collections' as seen in the Sheet - 'PIVOT' you will see other Pivots too.
Similarly I would like to create one single measure that can be used in all the pivots to determine the % of agents meeting target in each team, as well as in the last pivot that shows the %team wise bifurcation. Hope that helps a little. Appreciate your time and patience. @Matt Allington

TEST_FILE

 

[Matt Allington]

The formula shows the correct answer to the formula. If it is not what you need then the formula is wrong. Take a look at the attached.

https://www.dropbox.com/s/zly5w5af0wp1457/MREXCEL.xlsx?dl=1

 

[powerwill]

The formula shows the correct answer to the formula. If it is not what you need then the formula is wrong. Take a look at the attached.

https://www.dropbox.com/s/zly5w5af0wp1457/MREXCEL.xlsx?dl=1

Thank you @Matt Allington. The expression from the sheet you attached, works really well, excactly how I wanted!

Assuming this is a Dax expression, I tried to make it work in my Power BI dashboard, and the total of the Met% shows a SUM as opposed to an average, how do I fix this?

Again, sorry to keep you busy on a Saturday!