Forum Discussion
Grand total
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.
[MeasureSellout] is just sellout in your data, the formula:VAR __tmpTable = SUMMARIZE(ALLSELECTED(Table),[SoldToName],[Week],"__average",AVERAGE([Sellout])) return me this error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value
I don't see a RETURN function in there. If you use VAR, you must use RETURN.
Now im using this formula:
Average:=VAR _sellout = SUMMARIZE(ALLSELECTED(tabella);[SoldToName];[Week];"average";AVERAGE([Sellout]))
RETURN
AVERAGEX(_sellout;[average])
but the result is the same as before :
SoldToName W14 W15 W16 W17 W18 W19 W20 w21 w22 w23
x 1 1 1 1 1 1 1 1 1 y 1 1 1 1 1 1 1 1 1 z 1 1 1 1 1 1 1 1 1 i dont have a column that give to me the sum for the week, maybe beacause i put week on columns in the field of pivot table?
I'm not sure where else to go from here but I have attached the PBIX file. Seems to work for me with the parameters you have given. Hopefully you can figure it out from there.
it seems works on Power BI, maybe the problem is that i use power pivot on excel and than i create a pivto table?
Perhaps although I would expect that if your pivot table was configured like the matrix visualization in Power BI then it should work the same. I don't do Excel though.