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.

madhushree's avatar
madhushree
Frequent Visitor
9 years ago

Hi ,

 

We are building a report in power bi.

This is a matrix, where column is YYYY-MM. It also has 2 other columns 12M and 3M.

Here 3M =    ((Current month – Prev 3 Month)/prev 4 month)*100

For example –

3M = (column (2016-07)- column (2016-04)) / column (2016-03)*100

And

12M = ((Current Month – Prev 12 Month)/prev 13 month)*100

For example –

12M = (column (2016-07)- column (2015-07)) / column (2015-06)*100

 

We are not able to do the above calculations, Can you please us suggest how can this be done.

 

 

 

Thanks and Regards,

Madhushree.K.R