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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MauroBote
Helper I
Helper I

Running total for a dynamic percentage by date and category

Hi friends (and @dustinn)

 

Today I've posted a message asking some help and I had a solution Dynamic percentage by date and category, but now I need to add some aditional feature: a running total for a measure generated.

 

I had the following table:

original tableoriginal table

 

And I had to add a dynamic measure affected by DATE visual filter, so:

 

1) Make a Date table. At Modeling tab, click on New Table and insert the DAX:

Date = CALENDAR(MIN('Table'[Plan Date]),MAX('Table'[Plan Date]))

 

2) At Model on the left side, create a relationship between the 2 tables, Table[Plan Date] = Date[Date]

 

3) Create a measure: 

Measure = DIVIDE(SUM('Table'[Weight]),CALCULATE(SUM('Table'[Weight]),ALLEXCEPT('Table','Table'[Project],'Date'[Date])))
 
And its works perfectly, per example If I select visual filter between 01-01-2019 and 31-12-2019 the results is:
Imagen7.jpg
 
But now I need to add a new column: Measure Running Total (ordered ASC by PLAN DATE and grouped by Project).
 
Example 1. If the visual filter is blank, the table should be (in orange the new column):
Imagen9.jpg
 
Example 2. If the visual filter is between 01-01-2019 and 31-12-2019 the results should be:
Imagen8.jpg
 
Please your help!

 

 

 

 

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try this measure:

Measure RT = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER (
            ALLSELECTED('Table'),
            'Table'[PROJECT] in FILTERS('Table'[PROJECT]) &&
            'Table'[PLAN DATE] <= MIN ( 'Table'[PLAN DATE] ) 
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        ALLEXCEPT ( 'Table', 'Table'[Project], 'Date'[Date] )
    )
)

And the result shows:

11.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

View solution in original post

1 REPLY 1
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try this measure:

Measure RT = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        FILTER (
            ALLSELECTED('Table'),
            'Table'[PROJECT] in FILTERS('Table'[PROJECT]) &&
            'Table'[PLAN DATE] <= MIN ( 'Table'[PLAN DATE] ) 
        )
    ),
    CALCULATE (
        SUM ( 'Table'[Weight] ),
        ALLEXCEPT ( 'Table', 'Table'[Project], 'Date'[Date] )
    )
)

And the result shows:

11.PNG

Hope this helps.

 

Best Regards,

Giotto Zhi

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.