How to calculate cumulative Total and % in DAX?

Question

This might be very simple...

I have the below summary table in Power BI and need to build a Pareto Chart, what I'm looking for is a way to create columns "D" and "E"... Thanks in advance!

The Count from column "B" is a measure I've created in PBI based on multiple filters. I've already tried some Calculate/Sum/Filter type of expressions with no luck.

My raw data looks like Image #2... I have the measures to build the summary table with the exception of column "I" - Running % - (for which I will also need the cumulative total of events per bucket).

Unfortunately, I haven't been able to successfully apply the calculations from DAXPATTERNS.

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-10-24T20:45:45

There is a well-known pattern for cumulative calculations in the DAXPATTERNS blog.

Try this expression for Running % measure:

Running % =
CALCULATE (
    SUM ( [Percentage] ),
    FILTER ( ALL ( YourTable), YourTable[Bucket] <= MAX ( YourTable[Bucket] ) )
)

And try this for Cumulative count measure:

Cumulative Count =
CALCULATE (
    SUM ( [Count] ),
    FILTER ( ALL ( YourTable ), YourTable[Bucket] <= MAX ( YourTable[Bucket] ) )
)

Basically in each row you are summing those count or percent values that are less or equal than the bucket value in the evaluated row, which produces the cumulative total.

UPDATE: A posible solution matching your model.

Assuming your Event Count measure is defined as follows:

Event Count = COUNT(EventTable[Duration_Bucket])

You can create a cumulative count using CALCULATE function, which lets us calculate the Running % measure:

Cumulative Count =
CALCULATE (
    [Event Count],
    FILTER (
        ALL ( EventTable ),
        [Duration_Bucket] <= MAX ( EventTable[Duration_Bucket] )
    )
)

Now calculate the Running % measure using:

Running % =
DIVIDE (
    [Cumulative Count],
    CALCULATE ( [Event Count], ALL ( EventTable ) ),
    BLANK ()
)

You should get something like this in Power BI:

Table visualization

Bar chart visualization

Note my expressions use an EventTable which you should replace by the name of your table. Also note the running % line starts from 0 to 1 and there is only one Y-axis to the left.

Let me know if this helps.

How to calculate cumulative Total and % in DAX?

1 Answers