Stumped by row context? Or is it the filter context?

Anything related to PowerPivot and DAX Formuale
mstanger
Posts: 2
Joined: Thu Jul 30, 2020 12:19 pm

Stumped by row context? Or is it the filter context?

Postby mstanger » Thu Jul 30, 2020 12:54 pm

Hi: I thought I had a problem calculating weighted averages but I think I really just don't get evaluation context. (And I'm stumbling from one function to the next. I want to calculate the weighted average rent for apartments at various geographies: the property, the sub-market, and the city. My data is in two tables. One has the number of units (apartments) and their "average effective rent" by unit type (number of bedrooms) and property name. This table also came with percentages of units for each unit type in a property ("Mix Pct"). Here's a view:
[attachment=1]screenshot-blurred_data-avg_rent.png[/attachment]
The other table is a lookup for the sub-market and city.
I can use the Mix Pct to calculate a weighted average rent for the property, but I can't figure out how to get it for the sub-market or city. See my pivot table:
[attachment=0]screenshot-avg_rent.png[/attachment]
Rows 4, 19, 24, etc. are sub-markets. The sub-market totals for Total Units are correct, but the WAvg Eff Rents should be lower (unless the sub-market only has one property). The first sub-market (row 4) should have an average rent of $2,494, I believe.
I started trying to build the measure step by step so that I could divide the units in each row of the data table by the total units in the sub-market, the city, or other group (as shown in the Project Units and measure 1A in the pivot table). But similar to the first attempt, I can't figure out how to make the division work at the summary levels.
I think I want to do something like this:
MEASURE := DIVIDE ( [Total Units], SUMX ( Units, ... ) )
and then multiply each dividend by the average effective rent, iterating through the data table, but I don't know what expression to use in SUMX that will sum the units at every summary level (filter context) of the pivot table.
I hope I'm making sense enough that someone can help.
Thanks,
Mike
Attachments
screenshot-avg_rent.png
screenshot-avg_rent.png (156.9 KiB) Viewed 553 times
screenshot-blurred_data-avg_rent.png
screenshot-blurred_data-avg_rent.png (353.77 KiB) Viewed 553 times

MattAllington
Posts: 1154
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stumped by row context? Or is it the filter context?

Postby MattAllington » Fri Jul 31, 2020 8:31 am

It is not really possible to help you unless you post a sample workbook with anonymous data and the working you have already done and what you expect the outcome to be
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

mstanger
Posts: 2
Joined: Thu Jul 30, 2020 12:19 pm

Re: Stumped by row context? Or is it the filter context?

Postby mstanger » Tue Aug 04, 2020 1:55 am

Sorry, Matt--I thought what I posted would be sufficient--and thanks for the tip. I hope this will work.
[attachment=0]rent_data-powerpivotforum.xlsx[/attachment]
Attachments
rent_data-powerpivotforum.xlsx
(309.12 KiB) Downloaded 24 times

MattAllington
Posts: 1154
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Stumped by row context? Or is it the filter context?

Postby MattAllington » Sun Aug 09, 2020 9:12 am

That is the data - yes. But you haven't provided information on what the expected result is.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 6 guests