SUMX not returning the actual sum

[o59393]

Hi all,

I have a matrix with the following rows:

1. Facet

2. Tier 1: Process

3. Tier 2: Activity

The dax measure called ‘count if’ is correctly allocating 1 when the % is >=1% and 0 when below.

The issue I am facing is that the Tier 1 process row is not summing the 1’ correctly. Instead of 14 value (is summing the total rows of tier 2 activity) it should have a value of 1.

The dax measure is:

Count IF = 



var _x = 

SUMX(

    SUMMARIZE(

        Template,

        Template[Merged],

        Template[Hours by Year]

        ),       

        Template[Hours by Year])



var _y = 

        CALCULATE(

    [Non Duplicate Hours Process/Activity Numerator],

        ALLEXCEPT(

            Template,

            Template[Function],

            Template[Area],

            Template[Name]))

var _z = 



DIVIDE(_x,_y)



RETURN



    SUMX(

        DISTINCT(Template[Merged.4]),    

    IF(_z>=0.01,1,0)

    )

I guess the issue I have is with the last paragraph with the sumx.

Therefore my question is how can I get the sum of the 1’s and have the correct value on the tier 1 process row?

Template[Merged.4] is the combination of the columns: [Area], [Function], [#"Tier 1: Process Facet"], [#"Tier 2: Activity Facet"]

Thanks. 

[o59393]

Hi @jdbuchanan71 

Closer but still incorrect result of the total. Should be a 2 and it's returning the total number of activities for infrastructure capabilities:

Thanks

[Greg_Deckler]

@o59393 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[jdbuchanan71]

I wonder if you are having problems because of variables calculating outide the context of the last SUMX iterator.  Does it return the correct result if you try it like this?

Count IF =
SUMX (
    DISTINCT ( Template[Merged.4] ),
    IF (
        (
            VAR _x =
                SUMX (
                    SUMMARIZE ( Template, Template[Merged], Template[Hours by Year] ),
                    Template[Hours by Year]
                )
            VAR _y =
                CALCULATE (
                    [Non Duplicate Hours Process/Activity Numerator],
                    ALLEXCEPT ( Template, Template[Function], Template[Area], Template[Name] )
                )
            VAR _z =
                DIVIDE ( _x, _y )
            RETURN
                _z
        ) >= 0.01,
        1,
        0
    )
)

[o59393]

Hi @jdbuchanan71 

If this helps see attached the pbix.

The dax measure called count if, is returning 8 instead of 2:

COUNT_IF.pbix

Thanks.

[jdbuchanan71]

Does this measure return the result you are looking for?

SUMX Test = SUMX ( VALUES ( Template[Tier 2: Activity Facet] ), [Count IF] )