Search code examples
powerbidaxdata-analysispowerbi-desktopmeasure

Measure to calculate running total per row in PowerBI Matrix

I have a table with three fields: Year, Period and Product.

Year    Period  Product
2020    1   A
2020    2   C
2020    3   A
2020    3   B
2020    4   C
2021    2   B
2021    3   C
2021    4   D
2021    1   A
2021    1   C
2022    3   A
2022    4   C
2022    1   C
2022    2   D
2022    1   A
2023    4   C
2023    3   B
2023    2   C
2023    4   B
2023    1   A
2024    1   A
2024    4   B
2024    3   C

I want to pivot it in a way that the y-axis is the year, the columns are the development periods and the values are the running total of the count of products.

The DAX code that I managed to get from "Generate Quick Measure" is

Count of Product running total in Period =
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Product]),
    FILTER(
        ALLSELECTED('Sheet1'[Period]),
        ISONORAFTER('Sheet1'[Period], MAX('Sheet1'[Period]), DESC)
    )
)

enter image description here

Unfortunately, it seems the running total being calculated is wrong. For instance, for 2020, I expect the values to be (table on the right): 1,2,4,5 instead of 1,2,3,3.

I'm unable to figure out what is going wrong and would appreciate some pointers. The goal is the get running total values per row/year.

A .pbix file with the minimal example from above: https://drive.google.com/file/d/13AJy85-yDKhIv09huwqzmyCpdWXicPn_/view?usp=sharing

Solution

  • enter image description here

    Try this:

    Count of Product running total in Period = 
VAR x = MAX(Sheet1[Period])
RETURN
CALCULATE(
    COUNT('Sheet1'[Product]), 
    Sheet1[Period] <= x
)