AUM Analysis in PowerBi

About the Project

The dashboard was build in powerbi as asset evaluation dashboard for fund performance analysis for an investment firm

Live – Asset Evaluation Dashboard

How this dashboard is built

Assets under management is a headline number that hides two very different stories. AUM can rise because the fund won new money, or because the market moved in its favour. From a business point of view those are opposite outcomes, and a dashboard that only shows the total cannot tell them apart. This report separates the position from the movement, which is why the change measures matter as much as the value ones.

A single dense page, built for a daily or weekly review rather than exploration: the position at the top, movement in the middle, winners and losers at the bottom.

The data model

Table	Grain and role
Fund performance	The fact table. One row per stock per date, carrying the closing price, the daily percentage change and the fund value of the holding.
Ticker	Instrument dimension: stock name, stock ticker and sector. The sector column is what makes concentration analysis possible.
Fund Manager	The investor and manager dimension, so AUM can be attributed per investor rather than only in total.
Date table	Marked date table with year, month and day of month, driving the trend charts and the latest-value logic.
All Measures	Disconnected table holding the measures.

The modelling subtlety here is that AUM is a semi-additive measure. It adds up across stocks and investors, but it does not add up across time. Summing January and February gives you nothing meaningful, because a portfolio worth ten million in both months is worth ten million, not twenty. Every value measure has to take the position at a point in time rather than a sum over the period.

Column names below are taken from the model. These are the measure definitions as the model computes them, not a paste of the formula bar.

The measures

The position

Total AUM =
CALCULATE(
    SUM( 'Fund performance'[Fund Value] ),
    LASTDATE( 'Date table'[Date] )
)

Aum per investor =
DIVIDE( [Total AUM], DISTINCTCOUNT( 'Fund Manager'[Investor] ) )

LASTDATE is what handles the semi-additive problem: whatever period is selected, the measure returns the closing position rather than a running total. Average AUM per investor then answers the question a fund business actually cares about, which is whether the book is growing through more clients or bigger mandates.

The movement

Sum Fund Change =
SUMX(
    VALUES( Ticker[Stock Ticker] ),
    CALCULATE(
        SUM( 'Fund performance'[Fund Value] ) * SUM( 'Fund performance'[% change] ),
        LASTDATE( 'Date table'[Date] )
    )
)

Iterating per ticker rather than multiplying the totals is the important detail. A weighted change has to be computed at the level the weights exist, because a single blended percentage applied to the whole book would misstate the result whenever holdings are unevenly sized.

Ranking the movers

The top and bottom five charts are the same measure sorted in opposite directions, filtered by a TOPN in the visual rather than duplicated in DAX.

Top 5 by Change =
TOPN( 5, VALUES( Ticker[Stock Name] ), [Sum Fund Change], DESC )

Bottom 5 by Change =
TOPN( 5, VALUES( Ticker[Stock Name] ), [Sum Fund Change], ASC )

Walking the page

The layout follows the order the questions get asked in a review.

Cards across the top. Total AUM, AUM per investor and the aggregate change, giving the position and the day's movement before any detail.
Value of fund managed per investor. A bar chart showing distribution across the client base. Concentration risk lives here: a book where one investor holds most of the assets is a different business from one spread evenly.
Asset value per sector. A treemap, which is the right choice for showing proportion across a moderate number of categories at a glance, and immediately surfaces whether the fund is over-weighted anywhere.
Top 5 and bottom 5 stocks by change. Paired bar charts. Presenting both is the point: the winners alone would flatter, the losers alone would alarm.
Latest changes. A line chart tracking the movement over time, with area charts giving the trend on value.
Four slicers. Date, sector, investor and ticker, so any of the above can be narrowed without leaving the page.

Build decisions worth calling out

AUM is treated as semi-additive. LASTDATE rather than SUM over the period, so the number is a position and not a nonsense total.
Change is weighted per instrument. SUMX over tickers rather than applying one blended percentage to the whole book.
Winners and losers shown together. Symmetry keeps the page honest.
Sector is a dimension, not a column on the fact. Concentration analysis needs it to be filterable independently of the holdings.
Per-investor as well as total. Separates growth in clients from growth in mandate size.

What I would change next

The report shows what the portfolio is worth and how it moved, but not whether that movement was good. The missing piece is a benchmark: return against an index over the same period, which is how fund performance is actually judged. On top of that I would add flows as a separate measure, splitting the change in AUM into market movement and net new money, since that distinction is the one the business is really asking about. Some basic risk framing would round it out, even just volatility of daily change and the share of the book held in the largest five positions.