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 calculates the Contribution 

Power Query Added Column:

 

= Table.AddColumn(#"Changed Type1", "Contribution", each [Days]/List.Sum(#"Changed Type1"[Days]) * List.Average(#"Changed Type1"[Days]))

 

 

I think because this is essentially hard coded its not dynamic so when i want to view differently it doesnt give me the correct output. 

 

When i look at the full year data i get an average (Calculated Field Contribution) of 12.9 days.

If in POWER BI i then use a filter to only look at Q1 for example it incorrectly shows me 3.1 days instead of 12.5 days. 

 

I have copied sample data below and how i expected it to calculate, apologies the format does not look the best i was unsure how to add as an attachment.

 

I would like to create a measure in Power BI that is dynamic and ideally remove the calculated column, however the file still operates at a good speed if i need to keep it. 

 

Any help would be appreciated as i have been trying to solve this for about a week. 

 

Full Year View  Annual View   
Terms QuarterDays CALCULATED FIELD Contribution  Quarterly SummaryContribution 
A10Q17                                               0.22 Q1100                   3.1   
A10Q28                                               0.25 Q292                   2.9   
A10Q38                                               0.25 Q385                   2.7   
A10Q47                                               0.22 Q4137                   4.3   
A10Q18                                               0.25  414                 12.9   
A10Q47                                               0.22       
A10Q17                                               0.22 looking at specific quarters 
A10Q28                                               0.25 Q110012.5   
A10Q37                                               0.22 Q292               13.14   
A10Q47                                               0.22       
A10Q18                                               0.25 Reference Terms QuarterDays Contribution  
A10Q27                                               0.22 AA10Q17                                                          0.88 
A10Q38                                               0.25 AA10Q18                                                          1.00 
A10Q47                                               0.22 AA10Q17                                                          0.88 
Z20Q16                                               0.19 AA10Q18                                                          1.00 
Z20Q28                                               0.25 BZ20Q16                                                          0.75 
Z20Q33                                               0.09 BZ20Q18                                                          1.00 
Z20Q47                                               0.22 CZ20Q149                                                          6.13 
Z20Q18                                               0.25 CA10Q17                                                          0.88 
Z20Q27                                               0.22   Total Days100                                                        12.50 
Z20Q38                                               0.25   Average Days12.5  
Z20Q437                                               1.16       
Z20Q149                                               1.53       
Z20Q246                                               1.44       
Z20Q343                                               1.34 Reference Terms QuarterDays CALCULATED FIELD Contribution 
A10Q443                                               1.34 AA10Q28                                                          1.14 
A10Q17                                               0.22 AA10Q28                                                          1.14 
A10Q28                                               0.25 AA10Q27                                                          1.00 
A10Q38                                               0.25 BZ20Q28                                                          1.14 
A10Q47                                               0.22 BZ20Q27                                                          1.00 
A10Q48                                               0.25 CZ20Q246                                                          6.57 
A10Q47                                               0.22 CA10Q28                                                          1.14 
       Total Days92                                                        13.14 
 Total Days414                                            12.94   Average Days13.14286  
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.

     

8 Replies

  • 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.