Forum Discussion
Emulating an array
Hi!!!
I need some help!
I have a trouble in many dashboards creating a measure that emulates an array.
For eample, imagine we have a table like the following, where each value of the cells is in fact a sum of values. This is very important because if each value you see was just one value, the problem would be easy to fix.
| sum of M 1 | sum of M 2 | sum of M 3 | sum of M 4 | sum of M 5 | sum of M 6 | Total | Mean | |
| Concept 1 | 5 | 7 | 4 | 3 | 6 | 5 | 30 | 5,00 |
| Concept 2 | 1 | 5 | 3 | 7 | 4 | 8 | 28 | 4,67 |
| Concept 3 | 2 | 1 | 3 | 4 | 3 | 4 | 17 | 2,83 |
As you can see, the Total column shows the sum of each line.
But what if what I want to do is to sum only the cells where the sum is below the mean.
For example, if in concept 1 the sum of Mont 2 is 7, this value has to disappear from the list.
The result should be as follows.
| sum of M 1 | sum of M 2 | sum of M 3 | sum of M 4 | sum of M 5 | sum of M 6 | Total | |
| Concept 1 | 5 | 4 | 3 | 5 | 17 | ||
| Concept 2 | 1 | 3 | 4 | 8 | |||
| Concept 3 | 2 | 1 | 3 |
As you can see, the new Total excludes cells where the sum (not the values) is over the mean.
Thanks for your responses!!!
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!
9 Replies
DObiol , Are these different measures
amitchandak , No, it's the same measure for all the cells. Just the sum of different values, containing month and concept.
DObiol , replace Measure and Avgmeasure with you correct measure and mean measure
Measure = sum(Table[Value])
Avgmeasure =calculate(average(Table[Value]),allexcept(Table,Table[concept]))Above Avg =
sumx(filter(summarize(Table, Table[concept], Table[Month],"_1",[Measure],"_2",[Avgmeasure]), [_1]<=[_2]),[_1])
