Forum Discussion

robertofilho's avatar
robertofilho
Regular Visitor
6 years ago
Solved

WROG RESULT IN AVERAGE

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?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • robertofilho's avatar
      robertofilho
      Regular Visitor
      6 years ago

      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.