Cumulative sum by Country, Year and Quarter filter

[dofrancis3]

Dear Colleagues,

I am a newbie to Powerbi and want to create a line chart graph that shows a cumulative total broken down by Country, years and Quarter but the graph would show it on a country, I also would like to have a filter option that when selected would only show me the figures for that "category".  See table below:

Outpout

[DataNinja777]

Hi @dofrancis3 ,

To create a cumulative total line chart in Power BI, it's best to use a star schema to ensure efficient filtering and performance. The data model should include a fact table for transactions and dimension tables for dates, countries, and categories. The fact table (Transactions) should store Date, Country, Category, and Sales values. A Calendar table is necessary to handle time intelligence calculations, which can be created using the following DAX formula:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Year_Quarter", FORMAT([Date], "YYYY") & " Q" & FORMAT([Date], "Q")
)

This Calendar table should be linked to the Transactions[Date] column. Additionally, a Country table and a Category table should be created to store unique country and category values, establishing relationships with the fact table. Once the star schema is set up, a cumulative total measure can be written in DAX to compute running totals over time:

Cumulative Sales = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR CurrentCountry = SELECTEDVALUE('Country'[Country])

RETURN
CALCULATE(
    SUM('Transactions'[Sales]),
    FILTER(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= CurrentDate
    ),
    FILTER(
        ALLSELECTED('Transactions'),
        'Transactions'[Country] = CurrentCountry
    )
)

To visualize the cumulative total, a line chart should be used. The Calendar[Year_Quarter] field should be placed on the X-axis, while the Cumulative Sales measure is assigned to the Y-axis. The Country[Country] field should be used as a legend to differentiate cumulative totals by country. To enable filtering by category, a slicer should be added for Category[Category], allowing users to refine the results dynamically. This setup ensures that the cumulative total updates correctly while maintaining efficient filtering, leveraging Power BI's data model for optimal performance.

Best regards,