cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lbryce14
Frequent Visitor

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  
2 ACCEPTED SOLUTIONS
v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

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.

View solution in original post

v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

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:

martrix.png

 

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.

 

View solution in original post

8 REPLIES 8
v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

You can create a measure like this:

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

re.png

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.

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

 

 

Lbryce14_0-1614342644233.png

 

v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

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.

View solution in original post

@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 

Lbryce14_0-1614613240810.png

 

 

v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

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.

avgerage.png

 

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 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)

 

 

Lbryce14_0-1614685939645.png

 

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] )
        
    )
)

Lbryce14_1-1614686096722.png

I really appreciate your ongoing help with this. 😊

v-yingjl
Community Support
Community Support

Hi @Lbryce14 ,

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:

martrix.png

 

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.

 

View solution in original post

@v-yingjl Thank you for all your help! 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.