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