calculate sum of if measure

[AI14]

Hi

How can i calculate sum of if measure - atm its adding 1 if true but its not summing total or even if i add to a graph it shows as 1 when it should be 5 for example

[tamerj1]

Hi @AI14 
Which measure? Would you please indicate in the screenshot? And which column(s) are used to slice by in this table visual?

[AI14]

Hi

these are measures i created and put in a table.

1st measure:

Duration Breach >= IF(

    SUMX(CLEAN_LIVE,CLEAN_LIVE[LiveCleanTime]) > SUMX('Task Rules','Task Rules'[Duration]), 1)

2nd Measure

Duration Breach <= IF(

    SUMX(CLEAN_LIVE,CLEAN_LIVE[LiveCleanTime]) < SUMX('Task Rules','Task Rules'[Duration]), 1)

3rd Measure 

Finish Late =

IF ( [Finish-STD] > SUMX ( 'Task Rules', 'Task Rules'[Latest End] ), 1 )

4th Measure:

Duration+Late = IF(AND(

    [Duration Breach >] = 1 , [Finish Late] = 1),2)

These Measures add a 1 or 2 (4th measure) if statement true however its not summing.

[tamerj1]

@AI14 

Thank you

at the very right of the table you have a column [Start of H.... apparently you have other columns to the left, whatare they? From which tables? Whare the relationships between the tables?

[AI14]

using 3 different tables all have active relationships, either 1-2-many or many-2-many, the rest are measures.

the calculations are correct and the measures are working correctly but not counting the amount of times the statement is true

[tamerj1 · Accepted solution]

@AI14 
Please let me explain. 

The IF statement measure is non-additive measure. In PowerBi the total cell is evaluated just the same as any other cell (it is NOT the sum of the cells above). In fact the total cell is just another cell with different filter context (or no filter context in case of a table visual total or a matrix's grand total).

Usually non-additive measures are not supposed to be additive such as averages and percentages. For example if I have a measure that calculates the price per unit for different stores ($/unit), summing this measure for different stores to get a total has absolutely no meaning.

However, in your case this total is required and has meaning therefore, we need to force additivity. But forcing additivity depends on the filter context of the visual (the SUMMARIZECOLUMNS table created by power bi when you place the columns in the table visual). We need to manually create this table, iterate over it in order to sum the visible values in the visual.

I still don't have a clear idea about what you have there in the visual and the relationships between the different tables involved however, it would be something like

Duration Breach > =
SUMX (
    SUMMARIZE (
        FactTable,
        DimTable1[Column1],
        DimTable2[Column2],
        DimTable3[Column3]
    ),
    CALCULATE (
        IF (
            SUMX ( CLEAN_LIVE, CLEAN_LIVE[LiveCleanTime] )
                > SUMX ( 'Task Rules', 'Task Rules'[Duration] ),
            1
        )
    )
)

[AI14]

Amazing, Works like a charm !!

thanks for the explanation too, made alot of difference👍