cancel
Showing results for
Did you mean:

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

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.

original 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:

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

Example 2. If the visual filter is between 01-01-2019 and 31-12-2019 the results should be:

1 ACCEPTED SOLUTION
Community Support

Hi,

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:

Hope this helps.

Best Regards,

Giotto Zhi

Community Support

Hi,

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:

Hope this helps.

Best Regards,

Giotto Zhi

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors