Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter documents with all results

Hello,

I have a data table like this:

 

I need to calculate average of TestResult for all test types, but only for documents, having all test result values.

Also I need to create a table visual showing Document No. and AVG of Test result, but this table has to filter out documents having not all TestResults. I tried to create measure with summarize function and use it as visual level filter and it works for line level, but total is not calculated correctly. 

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can try this measure:

     

    AVG = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Document No.],
        'Table'[TestType],
        'Table'[TestResult],
        "flag", IF (
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[TestResult] <> BLANK ()
                        && 'Table'[Document No.] = EARLIER ( 'Table'[Document No.] )
                )
            )
                = COUNTROWS (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Document No.] = EARLIER ( 'Table'[Document No.] )
                    )
                ),
            1,
            0
        )
    )
RETURN
    AVERAGEX ( FILTER ( tab, [flag] = 1 ), [TestResult] )

     

    Sample file is attached that hopes to help you, please check and try it: Filter documents with all results.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous it should be a simple measure

     

    Avg = CALCULATE ( AVERGAE ( Table[Test Results] ), Table[Test Results] <> BLANK() )

     

    drop table visual and document number and above measure in it, and you will see the results.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k  simple measure will not eliminate Doc1 and Doc3 results from average calculation, and these should not be included. 

       

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    You can try this measure:

     

    AVG = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Document No.],
        'Table'[TestType],
        'Table'[TestResult],
        "flag", IF (
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[TestResult] <> BLANK ()
                        && 'Table'[Document No.] = EARLIER ( 'Table'[Document No.] )
                )
            )
                = COUNTROWS (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Document No.] = EARLIER ( 'Table'[Document No.] )
                    )
                ),
            1,
            0
        )
    )
RETURN
    AVERAGEX ( FILTER ( tab, [flag] = 1 ), [TestResult] )

     

    Sample file is attached that hopes to help you, please check and try it: Filter documents with all results.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.