Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
FPP
Helper I
Helper I

Sum of a running total by Category

Hi,
I have report wheer the user chooses a year and Month (Example March, 2024) via a filter.  It displays Product and Sales for that Month.  It also has a running total for the Sales from the start of time.  All of this works.

ProductSalesTotal Sales
Radio1002,500
TV4,2009,300
Toaster50500

I have a card that shows the complete totals.  It shows:

Sales: 4,350Total Sales: 9,300


Total Sales should show: 12,300.

The

 

 

 

Total Sales = 

VAR SDt = SELECTEDVALUE ('DateTble'[Dt])

// Create a Running total of Sales by Product
VAR Result =
    CALCULATE (    
        SUM ('SalesTable'[Loss Incurred]),
        FILTER (ALL('SalesTable'), 'SalesTable'[AcctDt] <= SDt && 'SalesTable'[ProductID] = MAX ('SalesTable'[ProductID]))
    )

RETURN Result

 

 

 

How do I get the correct overall total?

5 REPLIES 5
v-kongfanf-msft
Community Support
Community Support

Hi @FPP ,

 

What does your data model look like, please describe it with relevant screenshot information.

 

Best Regards,
Adamk Kong

v-kongfanf-msft
Community Support
Community Support

Thanks for the reply from @amitchandak , please allow me to provide another insight:

 

Hi @FPP ,

 

You can achieve a similar effect with the help of hasonevalue function. Refer to below formula:

TotalSales =
VAR SDt =
    SELECTEDVALUE ( DateTable[Dt] )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= SDt
                && 'Table'[Product] = MAX ( 'Table'[Product] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Product] ),
        Result,
        SUMX ( FILTER ( ALL ( 'Table' ), 'Table'[Date] <= SDt ), [Sales] )
    )

 

vkongfanfmsft_0-1715320363916.png

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, I do not know why but now my data is duplicating inside a different table visual.
This table has:

ProductDtSalesTotal Sales
TVMarch, 20244,2009,300
TVApril, 2024 9,300
etc.   



amitchandak
Super User
Super User

@FPP , Try like, measure should use date table in filter

 

Total Sales =

VAR SDt = SELECTEDVALUE ('DateTble'[Dt])

// Create a Running total of Sales by Product
VAR Result =
CALCULATE (
SUM ('SalesTable'[Loss Incurred]),
FILTER (ALL('DateTble'), 'DateTble'[Dt]] <= SDt )
)

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi, This solution also duplicates my data inside a different table visual.
This table has:

ProductDtSalesTotal Sales
TVMarch, 20244,2009,300
TVApril, 2024 9,300
etc.   

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.