Forum Discussion
Emulating an array
DObiol add the following three measures and you will get it, breaking it down to easily understand everything, use the last measure in your visual
Sum Sales = SUM ( Mean[Sales] ) Sales Mean = DIVIDE ( CALCULATE ( [Sum Sales], ALL ( Mean[Month] ) ), CALCULATE ( DISTINCTCOUNT ( Mean[Month] ), ALL ( Mean[Month] ) ) ) Sales below Mean = SUMX ( SUMMARIZE ( Mean, Mean[Country], Mean[Month] ), IF ( [Sum Sales] < [Sales Mea], [Sum Sales] ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Hi again amitchandak and parry2k ,
I will try to explain it better.
The idea is that I have a matrix where the columns are sales and the rows are countries.
The objetive is making a predictive calculation, and to do it, I need to filter the extraordinary months (in our case, to make it simpler, we are filtering months that ara above the mean).
The input table is the following:
| Sales | Month | Country |
| 1,00 | 1 | Spain |
| 6,00 | 1 | Spain |
| 5,00 | 1 | Spain |
| 3,00 | 2 | Spain |
| 8,00 | 2 | Spain |
| 7,00 | 2 | Spain |
| 4,00 | 3 | Spain |
| 6,00 | 3 | Spain |
| 9,00 | 3 | Spain |
| 6,00 | 4 | Spain |
| 3,00 | 4 | Spain |
| 2,00 | 4 | Spain |
| 5,00 | 5 | Spain |
| 6,00 | 5 | Spain |
| 8,00 | 5 | Spain |
| 1,50 | 1 | France |
| 9,00 | 1 | France |
| 7,50 | 1 | France |
| 4,50 | 2 | France |
| 12,00 | 2 | France |
| 10,50 | 2 | France |
| 6,00 | 3 | France |
| 9,00 | 3 | France |
| 13,50 | 3 | France |
| 9,00 | 4 | France |
| 4,50 | 4 | France |
| 3,00 | 4 | France |
| 7,50 | 5 | France |
| 9,00 | 5 | France |
| 12,00 | 5 | France |
| 1,25 | 1 | Italy |
| 7,50 | 1 | Italy |
| 6,25 | 1 | Italy |
| 3,75 | 2 | Italy |
| 10,00 | 2 | Italy |
| 8,75 | 2 | Italy |
| 5,00 | 3 | Italy |
| 7,50 | 3 | Italy |
| 11,25 | 3 | Italy |
| 7,50 | 4 | Italy |
| 3,75 | 4 | Italy |
| 2,50 | 4 | Italy |
| 6,25 | 5 | Italy |
| 7,50 | 5 | Italy |
| 10,00 | 5 | Italy |
The initial result for this input database should be this one:
| 1 | 2 | 3 | 4 | 5 | Total | Mean | |
| France | 18 | 27 | 28,5 | 16,5 | 28,5 | 118,5 | 23,7 |
| Italy | 15 | 22,5 | 23,75 | 13,75 | 23,75 | 98,75 | 19,75 |
| Spain | 12 | 18 | 19 | 11 | 19 | 79 | 15,8 |
But what I really want to get as outout is the following table, where the total column (this is the objective of the calculation) is just taking into consideration the sum of two months since tje other 3 are above the mean.
| 1 | 2 | 3 | 4 | 5 | Total | |
| France | 18 | 16,5 | 34,5 | |||
| Italy | 15 | 13,75 | 28,75 | |||
| Spain | 12 | 11 | 23 |
I guess now the problem is clearer.
Thx!
DObiol add the following three measures and you will get it, breaking it down to easily understand everything, use the last measure in your visual
Sum Sales = SUM ( Mean[Sales] )
Sales Mean = DIVIDE ( CALCULATE ( [Sum Sales], ALL ( Mean[Month] ) ), CALCULATE ( DISTINCTCOUNT ( Mean[Month] ), ALL ( Mean[Month] ) ) )
Sales below Mean = SUMX ( SUMMARIZE ( Mean, Mean[Country], Mean[Month] ), IF ( [Sum Sales] < [Sales Mea], [Sum Sales] ) )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!