Adding new rows into matrix table in power BI

[tjunjie1983]

I have table with columns such as 

1. change % (change in price of the forex pair)

2. fx pair (can be eurusd,gbpusd)

3. month 

4. year 

I made a matrix table, with slider as shown in picture 1. 

I want to find out how i can add additional rows of measurement for this matrix table that will look like (table 2,below) 

The rows i wanted to add are:

avgs = which is just the average of the values in the above column, e.g. from 2015 to 2024.

Stdev = stdev of this same column

pos% = % of positive value in this column. 

Please help, i tried many ways and i am stucked... 

If you do have alternatives to achieve ths same outcome . Please feel free to let me know. Thanks!

[Shivu-2000 · Accepted solution]

Hi @tjunjie1983,
I think this problem is similiar to yours and could be helpful to you.

https://community.fabric.microsoft.com/t5/Desktop/Possible-to-add-custom-rows-in-a-Matrix/td-p/21486...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[tjunjie1983]

Is anyone else able to help me on this ? thanks

[Anonymous · Accepted solution]

Thanks @Shivu-2000 

Hi, @tjunjie1983 

First of all, it is difficult to implement in the original table. You need to create a table with only a single column. To do this, I've created the following sample data:

Taking AVGS as an example, you need to add the current year and the rows you want the condition to the following column table as follows:

Use this column in the matrix:

Use the following DAX expression to create a measure that determines which row is currently in the line and executes the corresponding algorithm.

Measure = 
VAR _current = SELECTEDVALUE('Columns'[Year])
RETURN SWITCH(TRUE(),
    _current = "2022",CALCULATE(SUM('Table'[Percentage]),'Table'[Year]=2022),
    _current= "2023",CALCULATE(SUM('Table'[Percentage]),'Table'[Year]=2023),
    _current = "2024",CALCULATE(SUM('Table'[Percentage]),'Table'[Year]=2024),
    _current = "Avgs",
        VAR _current_month = SELECTEDVALUE('Table'[Month])
        RETURN CALCULATE(AVERAGE('Table'[Percentage]),'Table'[Month]=_current_month)
)

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shivu-2000]

Hi @tjunjie1983,
Avg Change =
VAR SelectedChange = AVERAGEX(YourDataTable,[Change %])
RETURN SelectedChange

-------------------------------------------------------------------
Stdev Change =
VAR SelectedChange = VALUES(YourDataTable[Change %])
RETURN SelectedChange
-------------------------------------------------------------------

Pos % =
VAR SelectedChange = CALCULATE(COUNTROWS(YourDataTable),YourDataTable[Change %] > 0)

VAR TotalCount = COUNTROWS(YourDataTable)
RETURN
DIVIDE(SelectedChange, TotalCount, 0) * 100

-------------------------------------------------------------------

These might be helpful.

If you find this insightful, please provide a Kudo and accept this as a solution.

[tjunjie1983]

Thanks for your help.

Yes, GPT told me the same . Anyway i tried your method again. But i cant put these new measure into rows

and i am getting this 

[Shivu-2000 · Accepted solution]

Hi @tjunjie1983,
I think this problem is similiar to yours and could be helpful to you.

https://community.fabric.microsoft.com/t5/Desktop/Possible-to-add-custom-rows-in-a-Matrix/td-p/21486...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[Shivu-2000]

Hi @tjunjie1983,

To acheive you desired output you will have to create 3 measures as: Average (Avg Change %), Standard Deviation (Stdev Change %), and Percentage Positive (Pos %). After that you will have to edit your matrix as:

• Go to the Fields pane in Power BI Desktop.
• Drag the newly created measures (Avg Change, Stdev Change, Pos %) to the Rows section of your matrix.
• You should now see these measures as additional rows in your matrix, alongside the existing "Change %" by FX Pair and Month/Year.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Happy to help!

[tjunjie1983]

to your point two, how should i create the average change, stdev change and pos % measures?