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.

     

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 

     

     

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

      Hi Anonymous ,

      Sorry for that I forgot to delete the column name in allselcted() function, the formula should be like this:

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

      Now it should work.

       

      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 thanks.  they work when they are individual table or Matrix but when i combine then it is not calculating the correct result. 


        Referencing one of the tables in my intial post, i am looking for it to show the days contribution when i analyse it by Quarter and Terms (also on their own which works)

         

         

         

        For example in my actual data (below)i would like to show the proportion of the contribution by quarter & terms i.e. Q1 should add up to 44.68 days not 11.52.  The total line in the below is showing correctly.

        I created a second measure and change the parameters to try and create this view 

         

        Measure_Contribution = 
CALCULATE (
    SUM(Table[Contribution]),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Terms] in DISTINCT('Table'[Terms]) &&
        'Table'[Quarter] IN DISTINCT ( 'Table'[Quarter] )
        
    )
)

        I really appreciate your ongoing help with this. ğŸ˜Š