cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## How can I do a calculation with the values shown in this matrix?

Please see this Matrix below:

If I need to calculate a percentage based on two of these fields can it be done across the board?

In other words, is there away to divde the line "# of Bounces" by "# emails Sent" to come up with a percentage?

Even if it resides out side of the matrix, maybe in it's own table?  (and do it for each column?)

Thanks for looking at this.

3 REPLIES 3
Microsoft

I make a test using the following sample data.

Firstly, add a index column in Query Editor of Power BI Desktop.

Secondly, create a column using DAX below.

prevalue = IF(
Table1[Type]=LOOKUPVALUE(Table1[Type],Table1[Index],Table1[Index]-1),
LOOKUPVALUE(Table1[Value],Table1[Index],Table1[Index]-1)
,0
)

Thirdly, calculate percentage using the following formula.

% percentage = DIVIDE(Table1[Value],Table1[prevalue],0)

If the above steps don't help, please help to post your table stucture and sample data.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Thanks Lydia Zhang

I am a little confused (i am new to Power BI).  I actually have two posts going.  One is this one, to try and create the percentages I need dynamically when the matrix is built.

The second, I created a query (another dataset based of the same table) where I pre-calculate the percentages and try and show those amounts in it's own matrix.  However, all though Property, Channel, Campaign and BeginMonth are present in both views (tables), the slicers will not apply to the second matrix.   I did not want to bring this up here, because trying two different methods will be confusing. I only mention it so perhaps one way is better than the other and I should pursue just one. See this post if you want to try and figure out why interactions are not working:

https://community.powerbi.com/t5/Desktop/Matrix-and-Slicers/td-p/168123

But back to this post...

using # of Bounces and # email Sent as an example:

what am i doing with?

prevalue = IF(
Table1[Type]=LOOKUPVALUE(Table1[Type],Table1[Index],Table1[Index]-1),
LOOKUPVALUE(Table1[Value],Table1[Index],Table1[Index]-1)
,0
)

Data sample:

From this I create measure to display this matrix:

Input Record with new measures.

Thank you

Microsoft

Since you post another thread with same question, your thread will be solved in another thread.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors