Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Weighted Average Recalculation when filtered

Good Evening,   I am trying to work out the contribution of the calculation of Days taken to pay when I apply various filters.  looking at the total data set, I have a column in Power Query that c...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    You can add another filter condition in the formula:

    Measure_Contribution =
CALCULATE (
    AVERAGE ( 'Table'[Days] ),
    FILTER (
        ALLSELECTED ( 'Table'[Quarter] ),
        'Table'[Quarter] IN DISTINCT ( 'Table'[Quarter] ) &&
        'Table'[Terms] IN DISTINCT ( 'Table'[Terms] )
    )
)

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    You should want to correct the total value so that the values in the matrix shows the average value, and the total shows the sum value.

    Besides the average measure previous and the second measure you have created by yourself, you can create this measrue to combine them to adjust the column total value in matrix:

    Re =
IF (
    HASONEVALUE ( 'Table'[Terms] ),
    [Average_Contribution],
    [Sum_Contribution]
)

    The result and the comparsion should be like this:

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi Anonymous ,

You can create a measure like this:

Measure_Contribution =
CALCULATE (
    AVERAGE ( 'Table'[Days] ),
    FILTER (
        ALLSELECTED ( 'Table'[Quarter] ),
        'Table'[Quarter] IN DISTINCT ( 'Table'[Quarter] )
    )
)

Attached a sample file in the below, hopes it could help.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-yingjl  thank you that works well.  One more questions if you dont mind.  Is their a way to get it to perform correctly when i add in Terms to the table, or so i need to create a seperate measure to analyse Contribution against the Terms?

    I have added a screen shot of the amended view below.  


    Thank you very much, thats a great help already. 

     

     

     

    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      Hi Anonymous ,

      You can add another filter condition in the formula:

      Measure_Contribution =
CALCULATE (
    AVERAGE ( 'Table'[Days] ),
    FILTER (
        ALLSELECTED ( 'Table'[Quarter] ),
        'Table'[Quarter] IN DISTINCT ( 'Table'[Quarter] ) &&
        'Table'[Terms] IN DISTINCT ( 'Table'[Terms] )
    )
)

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        v-yingjl  Thank you, however I am receiving the below error message " A Single value for column 'Terms' in table 'Table' cannot be determined...."  Is there a work around to this?

         

        Note -the Terms column is from the raw data, it is not a calculated column.

         

        Thanks