Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Running Totals

Hi all,

 

Hoping I can get some help on running total DAX measures. I've searched quite a few sites/resources and tried a few formulas but no luck with the usual SUMX, <=MAX filters. I suspect this could be due to trying to get the running total on an aggregated part number level (PN column) while the data is also broken down by period. New to DAX and Power Pivot so any further explanations/feedback on what I'm doing wrong/can be improved would be very helpful. The running totals will be used in the end for a ABC classification. Calculations are in Power Pivot and not Power BI, not sure if this is the right forum, please let me know if not.

 

Screenshot below of what I'm looking for + data structure + link to sample file with all of the DAX measures: https://docs.google.com/spreadsheets/d/1A157-P-nilTlUxKmDB57aYGmp6uU1NCY/edit?usp=share_link&ouid=113130596801483369843&rtpof=true&sd=true 

Edit: Do note that the file also includes the outcome of the formula provided by bhelou below.

 

Adding in the various formulas I've tried here:

 

Test_CumulativeSales1

=VAR MaxSales = MAX(f_1a_3PS[Sales])
RETURN CALCULATE(SUM(f_1a_3PS[Sales]), f_1a_3PS[Sales] <= MaxSales, ALLEXCEPT(f_1a_3PS, f_1a_3PS[PN]))

 

Test_CumulativeSales2

SUMX(FILTER(ALLEXCEPT(f_1a_3PS, f_1a_3PS[PN]), f_1a_3PS[Sales] <= MAX(f_1a_3PS[Sales])), f_1a_3PS[Sales])

 

Test_Cumulative%

=VAR SalesPercent = DIVIDE(SUM(f_1a_3PS[Sales]), CALCULATE(SUM(f_1a_3PS[Sales]), ALL(f_1a_3PS)))
RETURN SUMX(FILTER(ALL(f_1a_3PS[Sales]),
	f_1a_3PS[Sales] <= f_1a_3PS[Sales]), SalesPercent)

 

 

 

Please let me know if more info/clarification is needed. Thanks.

 

Need Help

2 Replies

  • bhelou's avatar
    bhelou
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Hi , 

    Can you try this and tell me if it workds :

    Running Total =
    VAR CurrentPN = SELECTEDVALUE('Table'[PN])
    RETURN
    CALCULATE(
    SUM('Table'[Volume]),
    FILTER(
    ALLSELECTED('Table'),
    'Table'[PN] = CurrentPN &&
    'Table'[Period] <= MAX('Table'[Period])
    )
    )


    This measure calculates the running total of the "Volume" column for each "PN" value up to the current "Period" value. It uses the MAX function to determine the maximum "Period" value selected in the visual.