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.

DataMatrix

The measure(s) used: % Share = SUM(Sheet5[Value])/[Total Value]

Total Value = CALCULATE(SUM(Sheet5[Value]),ALLSELECTED())

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.

Any help on this highly appreciated.

Regards,

Niket Talati

Need Help

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.

10 Replies

ankitpatira
Community Champion
10 years ago
Anonymous You can take this alternate approach as well. In power bi desktop go to query editor, select Year column from your dataset, under Transform tab, click Pivot Column, select Value for Values column, Don't Aggregrate under Advanced Options and click Ok.

This will give you your dataset like as shown below, Hit Close & Apply.

Then use Matrix visual and drop Market and Year fields. Click dropdown arrow on year fields dropped onto Values field and click Quick Calc.

Select Percentage of grand total under show value as and click Ok. This will make matrix visual appear as below.
Greg_Deckler
Community Champion
10 years ago
In your CALCULATE formula, try changing ALLSELECTED to ALL(Sheet5)
Baskar
Resident Rockstar
10 years ago
Hi Niket

% Share = SUM(Sheet5[Value]) / SUMX(ALL("Sheet5 Tablename",Sheet5[Value])

Try this , and let me know
Eric_Zhang
Microsoft Employee
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
Memorable Member
10 years ago
Why not use the quick calc in available in the may update?
- 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
    Microsoft 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.
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