Partners filmexxx.link, sextotal.net, ahmedxnxx.com, https://xnxx1xvideo.com, russianxnxx

A common problem is calculating running total in Power BI. You can possibly do a running total against Dates (very common) and against Non-Date Values like Products or Customers (which is not so common).

Let’s tackle both of these!

 

Running Total Video

 

Consider this Data Model

Running Total in Power BI - Data Model

The Calendar (Date) and Products table are linked to the Sales forming a one to many relationship.

Now consider this Pivot Table, where I have placed Years and Months (from the Calendar Table) and a Measure for Total Sales

Pivot Table with Sales

The requirement is to create a running total across all months until the end.

 

Running Total for Dates

Here is the measure that works

Monthly Running Total = 
IF(
    [Total Sales] <> BLANK(),
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL('Calendar'[Date]),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )
)

Running Total Measure

The Logic for the above measure goes something like this

  1. Find the last day of the month which is MAX(Calendar[Date]).
  2. Then see how many dates in the entire calendar (date column) are less then equal to the last day of the month.
  3. Calculate Total Sales for all Dates that match the above criteria.

This is very difficult! Now let’s move on to the more tricky and less common problem – Running Total against Text Column

 

Running Total for Non Date or Text Values in Power BI

We again start with a pivot table but this time we have Products sorted in descending order of Total Sales

Running Total for Non Dates

The Logic

  1. The problem with text values is that you cannot write a condition to return all text values <= max text value. That doesn’t work!
  2. So I’ll have to assign a rank (number) to each product in order of Total Sales.
  3. Then I can expand my condition to sum all sales values <= to the rank of the current product.

Here is an interim measure that calculates the rank of each Product in the order of Total Sales

Product Running Total =
    RANKX(
        ALL(Products[Product]),
        [Total Sales],,
        DESC,
        Dense
    )

See the results..

Ranking Measure

Alright let’s build this measure further and sum all values <= to the rank of each product displayed, that would be a Running Total for the Products.

See this revised measure

Product Running Total = 
VAR PdtRank =
    RANKX(
        ALL(Products[Product]),
        [Total Sales],,
        DESC,
        Dense
    )
VAR RunningTotal =
    CALCULATE(
        [Total Sales],
        FILTER(
            ALL(Products[Product]),
            PdtRank >= RANKX(
                        ALL(Products[Product]),
                        [Total Sales],,
                        DESC,
                        Dense
                    )
        )
    )
RETURN
    IF(
        [Total Sales] <> BLANK(),
        RunningTotal
    )

Now the above DAX certainly doesn’t look friendly but focus on the highlighted part.

  1. The FILTER function creates a table for all the products which are <= to the current rank of the product.
  2. That is pretty much all I need to wrap the CALCULATE around to get me the running total of Sales across Products.

See the results

Running Total for Text Values

 

    ⬇️ Pop in your Name & Email to get the file!


     

    A few more DAX Calculations

    1. Calculate Fiscal Year in Power BI
    2. Calculate Growth over Previous Non-Consecutive Date
    3. Calculate Percentiles in Power BI
    4. Change a Measure using Slicer
    5. Create Slab / Tier based Calculations
    6. Top Product Analysis
    7. Bottom Product Analysis

     



    Topics that I write about...