Forum Discussion
Grand total
I still do no know what you want and I am not going to hand type a bunch of columns and numbers trying to fumble around to find the answer.
Post sample data in a format that I can copy and paste (not a picture, ideally text in a table). Then, provide the expected results for the data you presented. This allows me and other forum members to copy the data into Power BI and create the necessary formulas that result in what you expect to see. This removes ambiguity and is far less frustrating than hand typing a bunch of data and then guessing what exactly it is that you want to see.
so suppose this is my database :
| Year | Week | SoldToName | Sellout |
| 2018 | W23 | Customer X | 3 |
| 2018 | W23 | Customer X | 4 |
| 2018 | w24 | Customer X | 5 |
| 2018 | W25 | Customer X | 6 |
| 2018 | W26 | Customer X | 8 |
| 2018 | w27 | Customer X | 10 |
i want show this in a pivot table:
| SoldToName | Average sellout w23 | Average sellout w24 | Averagesellout w25 | Averages sellout of filtered week (Total Average) |
| Customer x | 3,5 | 5 | 6 | 4,83 |
the problem is that i can't create the last column (total average), thank for your help
OK, this is much clearer. Thank-you. So, then I have this last question, how are you doing the "Average sellout of w23"? Is that a measure or are you putting the weeks as columns?
If you have your matrix configured such that you have "SoldToName" in your Rows, "Week" in your Columns and "Average of Sellout" in your Values, then your measure would be:
Measure = VAR __tmpTable = SUMMARIZE(ALLSELECTED(Table),[SoldToName],[Week],"__average",AVERAGE([Sellout])) RETURN AVERAGEX(__tmpTable,[__average])
This creates a temp table with the selected items from your table summarized by customer and week. Then you just have to take the average of those entries to get the value you are looking for.
first of all, tankk you very much for your help,
my matrix is like this:
Year Week SoldToName Sellout 2018 W23 Customer X 3 2018 W23 Customer X 4 2018 w24 Customer X 5 2018 W25 Customer X 6 2018 W26 Customer X 8 2018 w27 Customer X 10 and i i try your formula:
=SUMMARIZE(ALLSELECTED(table);[SoldToName];table[Week];"Total average";AVERAGE([MeasureSellout]))
i have this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
what im doing wring?
This is the data that I am using in a table called "Sellout"
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUQo3MgaSzqXFJfm5qUUKEUCOsVKsDj55E1R5E3R5U1R5U3R5M1R5M3R5C1R5c3R5QwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, SoldToName = _t, Sellout = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", type text}, {"SoldToName", type text}, {"Sellout", Int64.Type}}) in #"Changed Type"Only thing that I can see in your formula is that I do not know what [MeasureSellout] is? Oh, and if you are only using that function that is going to return a table, that is why I setup a VAR and then did an AVERAGEX, that gets you to one value.