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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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