WROG RESULT IN AVERAGE

[robertofilho]

DATA BASE (each problem that is find, the system sum of then on the data base, line by line)

POSITON               QTD                  DATE

TILT                         1                      01/01

A                             2                      01/01

TILT                         3                      02/01

TILT                         4                      03/01

A                             1                      03/01

In summary, what is the problem?

          01/01     02/01    03/01                           AVERAGE IN EXCEL                          AVERAGE IN PBI

TILT      1             3            4                                          2,6                                                    2,6

A          2             0            4                                            2                                                       3

Why?

Look that in 02/01 there is no any problem found, but the system not register “0” as problem in a database, so PBI wont be import THESE ROWS , and the final result in average will be wrong.

I would like to know, if there is a way to solve this problem directly in PBI?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

hope this helps.

[robertofilho]

Thankyou my friend, was perfect!! I really did not know this function (HASONEVALUE). But your attach helped me a lot and solved my problem here. I will check some videos about this function, because I cant understand exactly how it work.

[Ashish_Mathur]

You are welcome.

[v-shex-msft]

HI @robertofilho,

AFAIK, power bi calculated and aggregate on existing records, but your '0' records did not really exist in your data table. (it is expanding by pivoted effect and column category, power bi calculate on real records count instead of field count)

You can try to use the following measure formula on value filed to replace the original data field:

Measure =
IF (
    ISFILTERED ( T2[DATE] ),
    SUM ( T2[QTD] ),
    DIVIDE (
        CALCULATE ( SUM ( T2[QTD] ), VALUES ( T2[POSITON] ) ),
        CALCULATE ( COUNTROWS ( VALUES ( T2[DATE] ) ), ALLSELECTED ( T2 ) )
    )
)

Regards,

Xiaoxin Sheng