Sum of a measure

[Anonymous]

Hi there

I'm trying to perform a sum based on a subtraction of two other measures. However, when I set a condition in a IF statement, the P Bi does not do the sum.
The measures goes as follow:

<> falta int
# tickets *-falta int = calculate ( [# tickets] , filter ( base , [subcategoria] <> "Projeto - Falta interação"))

= falta int
# tickets * falta int = calculate ( [# tickets] , filter ( base , [subcategoria] = "Projeto - Falta interação"))

Sim? = if( 
                and ( 
                    ( not isblank ( [# tickets *-falta int] ) ) = ( not isblank ( [# tickets * falta int] ) ) , 
                    not isblank ( [# tickets *-falta int] ) &&  not isblank ( [# tickets * falta int] ) ) , 
                    1  ,            -- TRUE 
                    blank () )      -- FALSE

 

The problem that i can't make it through is how to make the [Sim?] measure actually sum the values instead of showing a single value ( 1 ). The other measures do the sum, but not the [Sim?] one.

Any thoughts? I guess that the first two measures count the rows, and this can be the reason on why the last one does not perform the sum - it only indicates whether the value will be 1 or blank. Being this the actually reason why, how can I work this situation around and do the sum?

Thanks in advance!

[Anonymous]

@Anonymous , the [Sim?] measure you posted above has an IF statement that will only ever return a 1 or FALSE. It does not perform any addition or summing.

I assumed you have tried this?

Sim? = [# tickets *-falta int] + [# tickets * falta int]

And if you only want Sim? to display a value if both the other measures have a value:

Sim? =
    IF(
        ISBLANK([# tickets *-falta int]) || ISBLANK([# tickets * falta int]),
        BLANK(),
        [# tickets *-falta int] + [# tickets * falta int]
    )

[Anonymous]

Hey buddy

Actually by summing both measures the outcome wont be what I indeed want. The only difference in the formula you suggested is instead of summing 

  [# tickets *-falta int] + [# tickets * falta int]

it should return 1, and then do the sum of this measure.

Sim? =
    IF(
        ISBLANK([# tickets *-falta int]) || ISBLANK([# tickets * falta int]),
        BLANK(),
        1 -- when I try using either 1 or blank, the result is always 1 or blank
    )

The desired outcome goes like this:

[AnaJimenez]

Hi, can you show us how you need the table to look like?

[Anonymous]

Hey Ana @AnaJimenez 

I'd like to make a sum of these 1. For instance, there are 56 values that match what I've setted, therefore what I want to do is to create a measure that will sum and display this number of 56. Currently it's only showing a bunch of 1 (precisely 56 of these) without showing the final number.

[Anonymous · Accepted solution]

@Anonymous , try this measure for [Sim?]

Sim? =
    SUMX(
        VALUES('Base'[Telefone]),
        if( 
                and ( 
                    ( not isblank ( [# tickets *-falta int] ) ) = ( not isblank ( [# tickets * falta int] ) ) , 
                    not isblank ( [# tickets *-falta int] ) &&  not isblank ( [# tickets * falta int] ) ) , 
                    1  ,            -- TRUE 
                    blank ()        -- FALSE
        )
    )

Or to tidy up the logic the way I think it should be:

Sim? =
    SUMX(
        VALUES('Base'[Telefone]),
        if(not isblank ( [# tickets *-falta int] ) && not isblank ( [# tickets * falta int] ), 1)
    )

[Anonymous]

It has worked, my friend. Thank you.

Just one thing: when I put it into the table it worked just fine, however in a card it does not show the 75 the same way it did in the table. Do you know why it happened?

[Anonymous]

@Anonymous , I'm not sure why the measure doesn't work in a Card visual. Try putting a filter on the Card visual for 'Base'[Telefone] and try it with individual values, and with a range of values, and see if it works. This may help find where the issue is.

[Anonymous]

Even doing as you suggested me to, it does not worked properly into a visual card, however i'll be using it through a table for now. If I find a way to make it work I'll share it here. Once again, thank you very much.

Best regards.

Pedro H.