Grand total measure is 0 for IF forumla with 0 and 1 as results

[Anonymous]

Hello,

I'm stuck to the following issue. I need to count how many salesreps are really active regarding that rule : a salesrep who is active is a salesrep who detected at least the same amount of opportunities as his objective.

To do this I did the following mesure:

Actif IC 1 = IF([Nbre Opp]-[Obj. IC]<0 || ISBLANK([Nbre Opp]);0;1)

"Nbre Opp"= number of opportunities detected.

"Obj. IC" number of expected detected opportunies.

The formula works well but the problem is that the total in a table is 0 so it looks like PBI considers the IF DAX formula as 0 whatever the result value is 0 or 1. Format measure is "whole number" and so far I don't see any solution to my problem...

If someone has idea I would be very happy to get some help. Thanks very much to anybody with a litlle clue 🙂

 

[Zubair_Muhammad]

Hi @Anonymous

Try adding this MEASURE

measure =
IF (
    HASONEFILTER ( TableName[FullName] ),
    [Actif IC 1],
    SUMX ( ALLSELECTED ( TableName[FullName] ), [Actif IC 1] )
)

[Anonymous]

Hi @Zubair_Muhammad,

Thanks for your reply. Unefortunately the total is not correct: it calculates 9 which is not correct 😞

[Zubair_Muhammad]

@Anonymous

How about this one

measure =
IF (
    HASONEFILTER ( TableName[FullName] ),
    [Actif IC 1],
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( Table ),
            TableName[Month],
            TableName[Activite IC],
            TableName[FullName]
        ),
        [Actif IC 1]
    )
)

[Anonymous]

@Zubair_Muhammad

not applicable as what you see comes from different tables. Maybe I should be more precise.

- "month" comes from DimDate table (generated with calendar auto fx)

- "activité IC" comes from DimUser table

- "full name" comes from DimUser table

I tried to start from you last formula and readapt a little but without any sucess. And as I'm beginning with PBI and discovering DAX, it's quiet difficult...

[Anonymous]

Does someone has another clue. It really seems that the measure format is considered as text but it is not. Looks so simple in theory...