Bug In SUMX when using measures in expression?

[Anonymous]

Hello,

I've noticed that SUMX is calculating measures in what I believe to be an incorrect way.

I find the results below to be confusing and/or unintuitive, so I just want to confirm if this is a bug or not.

Here is what I want to achieve

• I would like to calculate the Firm revenue in the example data by multiplying [Hours] x [Rate] x [Weight] for each row
• "Firm" projects are those that are marked with [Type] = "Firm", and also ([Type] = "Opportunity" && [Weight] = 0.9)

Here is the results which I get from PBI.

Here is what I would expect (calculated in Excel) 

For individual I1, the summed revenue is 75000 higher in PBI than in Excel

My analysis

It seems that the measure is calculated for all "duplicated" entries/rows in the data.

In this example, the "Sum of Firm Hours" measure will sum the values from all identical rows, and not just the "current row".
So for the data rows {I1, Firm, 50, 50, 1}, the SUMX measure will evalute to (300 x 50) x 6 rows, instead of (50 x 50) x 6 rows.

The difference between these two calculations is 75000 in "revenue", which is also the difference between the Excel & PBI screenshots.

Here is the data I used in the example

Individual	Type	Hours	Rate	Weight
I1	Firm	100	50	1
I2	Firm	100	70	1
I3	Opportunity	100	80	0,4
I1	Firm	50	50	1
I2	Opportunity	100	70	0,9
I1	Opportunity	100	50	0,9
I1	Firm	50	50	1
I1	Firm	50	50	1
I1	Firm	50	50	1
I1	Firm	50	50	1
I1	Firm	50	50	1

Here are my defined measures

Sum of hours = SUM(Facttable[Hours])

Sum Firm hours = CALCULATE(
    [Sum of hours], 
    FILTER(Facttable, Facttable[Type] = "Firm")
) + 0.9 * CALCULATE(
    [Sum of hours], 
    FILTER(Facttable, Facttable[Type] = "Opportunity" && Facttable[Weight] = 0.9)
)

Firm Revenue = SUMX(
    Facttable,
    [Sum Firm hours] * Facttable[Rate]
)

Weighted Average charge out rate = DIVIDE([Firm Revenue], [Sum Firm hours])

[v-chuncz-msft]

@Anonymous 

You may refer to the DAX below. For any question about data shaping, modeling, and report building in the Power BI Desktop app, feel free to post a new message in Desktop forum.

Firm Revenue =
SUMX (
    FILTER (
        Facttable,
        Facttable[Type] = "Firm"
            || ( Facttable[Type] = "Opportunity"
            && Facttable[Weight] = 0.9 )
    ),
    Facttable[Hours] * Facttable[Rate] * Facttable[Weight]
)

[Anonymous]

@v-chuncz-msft,

Thanks for your response! I rewrote my measures similar to your suggestion already

I then take it that this is not considered a bug - Thanks!