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

[Anonymous]

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 

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.