User Profile
BdC2
New Member
Joined 1 year ago
User Widgets
Contributions
Re: Problem with Average
The solution that ended up working for my set of data was: TotalQtyPerWeek = CALCULATE( SUM('Table'[Qty on Hand]), ALLEXCEPT('Table','Table'[Location],'Table'[Date Week],'Table'[Product Name]) ) WeeksCount = CALCULATE( DISTINCTCOUNT('Table'[Data Week]), ALLEXCEPT('Tabke','Table'[Location],'Table'[Product Name]) ) AverageQtyPerWeek = DIVIDE( [TotalQtyPerWeek], [WeeksCount] )580Views0likes0CommentsRe: Problem with Average
I was still getting a very weird average, but I ended up adding some extra ALLEXCEPT conditions and it seems to have worked. DAX TotalQtyPerWeek = CALCULATE( SUM('Table'[Qty on Hand]), ALLEXCEPT('Table', 'Table'[Location], 'Table'[Data Week],'Table'[Product Name]) ) Create a measure for the number of weeks per location: DAX WeeksCount = CALCULATE( DISTINCTCOUNT('Table'[Data Week]), ALLEXCEPT('Table', 'Table'[Location],'Table'[Product Name]) ) thank you667Views1like2CommentsProblem with Average
I am quite new to PowerBI and really struggling with something I feel should be quite simple... Average... I have a set of data that has location, product name, Qty and Data Week. The normal Average function causes that ALL quatities are added and devided by the number of products, but I need an average of total quantity per location, per data week, regardless of the number of products in the list. I need th average to be based on the total quantity for each week. because I have various products in each data week, it is dividing by the number of lines and not the number of weeks, so essentially the total quantity needs to be divided in the below case by 2 (Data Week 1 and 2 = 2) and not the total number of lines. If that makes sense? Location Product Name Qty on Hand Data Week A Product 1 200 1 A Product 2 300 1 A Product 3 200 1 A Product 4 100 1 A Product 1 100 2 A Product 2 300 2 A Product 3 300 2 A Product 4 100 2 B Product 1 50 1 B Product 2 200 1 B Product 3 300 1 B Product 4 500 1 B Product 1 200 2 B Product 2 600 2 B Product 3 200 2 B Product 4 300 2Solved690Views0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.