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.

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity 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.