Emulating an array

[DObiol]

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!!!

[amitchandak]

@DObiol , Are these different measures

[DObiol]

@amitchandak , No, it's the same measure for all the cells. Just the sum of different values, containing month and concept.

[amitchandak]

@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])

[DObiol]

@amitchandak Thanks for your response.

However, I think it is not working because Avgmeasure would calculate the average of each of the values containing a cell and a line, not the average of the sum.

In the example, for example cell containing "concept1" and "month 1" equals 5 as you can see.

	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

But in fact, the value "5" is the sum of other values, in this case "1+2+2".

So, when you calculate the mean, the formula will take into consideration the mean of 1, 2 and 2 for this cell, and the same for the other cells.

The idea is considering a cell as a value to operate, although in fact it is a sum of values. That's why I understand that this is similar to an array in other languages.

Thx!!

[parry2k]

@DObiol I would recommend to post some sample data and expected output. 

[DObiol]

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!

[parry2k · Accepted solution]

@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!

[DObiol]

Hi @parry2k , that was great!

Thank you very much.

You really helped me.

[amitchandak]

@DObiol , share the data in raw format