DAX: Cumulative profit

04 Apr, 2023

80/20

I was doing some data analysis on Power BI on profit, for my company (here termed "gross margin"). As an extra challenge, I wanted to rank our customers, from most profitable to least, and then see what number of customers give us 80% of our profit.

The common "80/20" is usually applied to a lot of things, and one of those is "20% percent of our customers make us 80% of our profit". I wanted to see if that was true here.

For context, we are consultancy, and our business model is selling our consultants' expertise. Some jobs are lump sum, and some jobs are paid by the hour.

It could one of two ways. Perhaps we need more than 20% of our customers to make us our 80% profit. This may mean that we're underselling our services, and can afford to charge more. Or, less than 20% of our customers give us 80% of our profit. This may mean that we can afford to put less effort into the less profitable customers and more focus on the profitable ones.

Meet the cast

Tables

revenue is a table that looks like this:

For each project, at each reporting month, this table records the gross margin that that project made.

There is another table (projects) that relates projects with its related data, including customer:

Measures

There are already some other measures that I call upon in this measure.

The first is Gross Margin. Making this a measure instead just summing straight from the table ensures that the value is dynamic and will obey the correct contexts within calculations.

Then there is Rank Customer by GM. This does what it says on the tin - it takes all of the customers that are present in the revenue table, and ranks them by the gross margin.

Rank Customer by GM = RANKX(
    DISTINCT(
        SELECTCOLUMNS(
            ALL(revenue),
            "Customer", RELATED(projects[Customer])
        )
    ),
    [Gross Margin]
)

Final code

The full code for Percent of GM (cumulative, by customer) is below.

With this measure as a column in a table of customers, it allows us to scroll down, and, e.g., find out where the 80% mark is, and how many customers give us that 80% gross margin.

Percent of GM (cumulative, by customer) =
VAR totalGM = CALCULATE(
    [Gross Margin],
    ALL(revenue)
)

VAR cumulativeGM = SUMX(
    TOPN(
        [Rank Customer by GM],
        GROUPBY(
            ALL(revenue),
            projects[Customer],
            "Gross Margin", SUMX(CURRENTGROUP(), revenue[Gross Margin])
        ),
        [Gross Margin],
        DESC
    ),
    [Gross Margin]
)

RETURN DIVIDE(
    cumulativeGM,
    totalGM,
    0
)

#dax #powerbi