Forum Discussion

DObiol's avatar
DObiol
Regular Visitor
6 years ago
Solved

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 i...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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's avatar
DObiol
Regular Visitor
6 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    DObiol
    Regular Visitor
    6 years ago

    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 1sum of M 2sum of M 3sum of M 4sum of M 5sum of M 6TotalMean
    Concept 1574365305,00
    Concept 2153748284,67
    Concept 3213434172,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!!

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      DObiol , share the data in raw format

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

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

      • DObiol's avatar
        DObiol
        Regular Visitor
        6 years ago

        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:

        SalesMonthCountry
        1,001Spain
        6,001Spain
        5,001Spain
        3,002Spain
        8,002Spain
        7,002Spain
        4,003Spain
        6,003Spain
        9,003Spain
        6,004Spain
        3,004Spain
        2,004Spain
        5,005Spain
        6,005Spain
        8,005Spain
        1,501France
        9,001France
        7,501France
        4,502France
        12,002France
        10,502France
        6,003France
        9,003France
        13,503France
        9,004France
        4,504France
        3,004France
        7,505France
        9,005France
        12,005France
        1,251Italy
        7,501Italy
        6,251Italy
        3,752Italy
        10,002Italy
        8,752Italy
        5,003Italy
        7,503Italy
        11,253Italy
        7,504Italy
        3,754Italy
        2,504Italy
        6,255Italy
        7,505Italy
        10,005Italy

        The initial result for this input database should be this one:

         12345TotalMean
        France182728,516,528,5118,523,7
        Italy1522,523,7513,7523,7598,7519,75
        Spain12181911197915,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.

         12345Total
        France18  16,5 34,5
        Italy15  13,75 28,75
        Spain12  11 23

        I guess now the problem is clearer.

         

        Thx!