Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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

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

 

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.

@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

 

 

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

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.

 

@v-yingjl Thank you for all your help! 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.