cancel
Showing results for
Did you mean:
Frequent Visitor

How to do row over row calculation in Power BI matrix?

Hi,

I used the import query to load daily attendance into the Power BI. The raw data is shown in picture 1.

Picture 1

Then I counted the attendance number in different categories by matrics. As you can see from picture 2, the "Exhibit Attendance" column is the detailed categories of attendance. The rest of the columns are made by different measures, eg. the measure caculated the attendance number of month to date last year is [ Month to Date LY] = CALCULATE([Total Attendance],DATESBETWEEN('Dates'[Date],DATE(YEAR(MAX('Dates'[Date]))-1,

MONTH(MAX('Dates'[Date])), 1),DATE(YEAR(MAX('Dates'[Date]))-1,
MONTH(MAX('Dates'[Date])), DAY(MAX('Dates'[Date])))))Picture 2

In EXCEL, if I want to get the number of attendance (excluding school groups), I can do the row over row calculation easily by row 15-row13, and get the highlighted row in picture 3.Picture 3

So how can I achieve the same result in Power BI matrix?

Thanks,

Selene

3 REPLIES 3
Super Contributor

Re: How to do row over row calculation in Power BI matrix?

I think what you want and what you are asking for are actually different things.

What you want is a total, which excludes certain data based on a condition.  I'd recommend a measure to do this.  This will be in the form of a CALCULATE statement, using your original methodology, and applying a filter of "Exhibit attendance <> "School Group Visits"

This could also be done in its own visual by the advanced filtering section.

Proud to be a Datanaut!

Frequent Visitor

Re: How to do row over row calculation in Power BI matrix?

Hi Ross,

Thanks very much for your response. I think the case I shared is a bad one. What if I need to calcluate the percentage of one row over another row?

Picture 4 is one example that equals = row 22/row 15.Picture 4

Thanks!

Selene

Super Contributor

Re: How to do row over row calculation in Power BI matrix?

You could create a measure specifically designed to only return data for a specific scenario, such as "School Group Visits".  You can create a 3rd measure that does a calculation using the other two.

So as an example you could use the Calculate with Filter statement to create measure1 and measure2.  Measure3 could be something like  DIVIDE(Measure1, Measure2).  Then whenever you use measure 3, measure 1 and measure 2 will have the context needed to make the calculation work.

Proud to be a Datanaut!

Announcements

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 276 members 2,969 guests
Recent signins: