CODEWITHSUNDEEP
excelpivotdaxpowerpivot
user10732287
user10732287

Reputation:

Power Pivot - Dax Measure sums up wrong

together

somehow my pivot table is currently crashing. The table is structured as follows:

enter image description here

Area, Code and QTY1 are defined by the data model. QTY2, Min and Performance are measures. QTY2 sums all codes for defined filters from another table. Min calculates the lower value of QTY1 and QTY2. Measure Performance calculates the ratio of Min to QTY1.

Unfortunately, the sum of Min from Excel will be incorrect. Also, the mean or the sum of performance is also wrong.

Formumlar QTY2: =CALCULATE(SUM(tbl2[QTY]);FILTER(tbl2; tbl2[TYP]<>"11"))
Formular MIN: =MIN([QTY2];[QTY1])
Formular Performance : = [MIN]/[QTY1]

What exactly am I doing wrong? How can the mistake be avoided?

Edit: The following approach sums up the right volume for [Min]. But it is not showing the accurate average of 37%. It sums up the divided value.

[Performance]=SUMX(tbl_General;DIVIDE([QTY2];[QTY1];BLANK()))
[Min]=SUMX(tbl_General;(MIN([Qty1];[Qty2])))

Why is that so?

enter image description here Best regards Joshua

Upvotes: 0

Views: 154

Answers (1)

Frostytheswimmer
Frostytheswimmer

Reputation: 718

So this is an example of where SUMX is needed.

You've stumbled on the difference between the aggregation of an expression and the sum of values.

Something like SUMX(dim_Tbl, DIVIDE([MIN], [QTY1], BLANK())) should work

EDIT:

After seeing the edit on the OP, the following measures should work.

Min = SUMX(tbl_General;(MIN([Qty1];[Qty2])))

and 

Performance = DIVIDE([QTY2];[Min];BLANK())

In general, 'X' measures are used to iterate over a table and sum the table whereas 'normal' measures are used for recalculations in sums. Your Performance measure you want to recalculate for the total, so don't use SUMX, your Min Measure, you want as a sum of the previous values, so do use a SUMX.

Upvotes: 1

Related Questions