cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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 Employee

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)

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 Employee

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.