Sum of a running total by Category

[Anonymous]

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.

Product	Sales	Total Sales
Radio	100	2,500
TV	4,200	9,300
Toaster	50	500

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

Sales: 4,350

Total 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?

[Anonymous]

Hi @Anonymous ,

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

Best Regards,
Adamk Kong

[Anonymous]

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

Hi @Anonymous ,

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] )
    )

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.

[Anonymous]

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

Product	Dt	Sales	Total Sales
TV	March, 2024	4,200	9,300
TV	April, 2024		9,300
etc.

[amitchandak]

@Anonymous , 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 )
)

[Anonymous]

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

Product	Dt	Sales	Total Sales
TV	March, 2024	4,200	9,300
TV	April, 2024		9,300
etc.