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 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. ğŸ˜Š

v-yingjl's avatar
v-yingjl
Community Support
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 for all your help!