Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

How to create a calculated row in a matrix?

Hi All,   I am trying to calulate % share in matrix. My data is like below. I am using Matix - Market in Row, Year in Column and % Share (Calculated measure) in Values.       The measu...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Anonymous

     

     

     

    Here you can see the % share in the picture is wrong. How I want is, Market's share / total share for that year. I know this I can achieve through chaging measure - Total Value = CALCULATE(SUM(Sheet5[Value]),ALL(Sheet5[Market])) -> which I do not want because Market is static here. Means I do not want to give any field name in the formula, it should calculate based on which field I am using in rows of matrix because any field can be used in Rows.

    In this case, you can change the measure [total value] a little bit as below so that the [% share] can vary according to the row.

    Total Value = CALCULATE(SUM(Sheet5[value]),FILTER(ALL(Sheet5),Sheet5[year]=MIN(Sheet5[year])))

    But the total column of the matrix won't make sense in this case. You can hide it.

samdthompson's avatar
samdthompson
Icon for Memorable Member rankMemorable Member
10 years ago

Why not use the quick calc in available in the may update?

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Thank you so much for your responses.

 

Greg_Deckler : I tried, but it is giving me total (irrespective of year) so it is wrong. It should be divided by the total of that particular year.

 

Baskar: It is giving me same percentage value which I have attached. So doesn't help.

 

ankitpatira: Thanks for the detailed answer but in my actual data, I have around 100 years and many rows and columns. So I could not change this to pivot column, I need to do through DAX formulas.

 

Eric_Zhang: Thanks for the answer, but my users want to see total column also. So it doesn't help me.

 

samdthompson: I tried, doesn't help.

 

Any other solution?


Regards,

Niket Talati

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago
    talatiniket wrote:

    @Eric_Zhang: Thanks for the answer, but my users want to see total column also. So it doesn't help me.

     

     

    Any other solution?


    Regards,

    Niket Talati

    Anonymous

    Then as far as I know it is not possible in a matrix. By the way, things would be much easier if using individual [% share] and [Total Value] in individual matrixs for different rows, may I know why do you have to wrap all scenarios in one [% share] measure.

  • Baskar's avatar
    Baskar
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    Hi,

     

    in dax i cant find how to pick single cell value.

    if we can pick like this from DAX we can do, if not we cannot do .

     

    Dax fully based on table and column level from table.

     

    upto my knowledge used the axisting current DAX function we cant do , sorry :smileysad::smileysad:

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Eric_Zhang : My users want to have only one DAX for all scenarios (more flexible). If it is not possible then I will try to conveince with your approach.

       

      Baskar: Thank you for the help. No issues..will try to explain my user.

       

      Thank you all.

       

      Regards,

      Niket Talati