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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TI_25
New Member

Dynamic Calculated row

All, 

I'm trying to have dynamic calculated row showing the sum of specific number, in my case hours. 

My row shows sum of hours, I'm trying to indicate the sum of 50 hours out of the same row. Not sure how to indicate sum and show where 50 hours is, how to achieve this need help. Exmple underneed shows what I'm trying to achieve. 

 

TI_25_0-1736647977239.png

 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @TI_25 ,

 

To dynamically calculate the sum of hours and indicate the point where a cumulative total reaches 50 hours, you can achieve this in Power BI using DAX formulas. The process involves creating a measure to calculate a cumulative sum and another measure to determine the sum until it reaches 50 hours.

Start by creating a measure for the cumulative sum of hours. You can use the SUMX function combined with FILTER to ensure that each row accumulates the sum of all previous rows:

CumulativeHours = 
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Index] <= MAX('Table'[Index])
    ),
    'Table'[TOTAL_W/O_HOURS]
)

Next, create a measure to flag rows where the cumulative sum reaches or exceeds 50 hours. This helps identify the point where the threshold is crossed. The formula for this measure is:

Flag50Hours = 
IF([CumulativeHours] >= 50, 1, 0)

To show the sum until the cumulative total reaches 50 hours, you need a measure that dynamically calculates and stops adding values once the cumulative sum hits 50 hours. The formula for this measure is:

SumUntil50Hours = 
IF([CumulativeHours] <= 50, [TOTAL_W/O_HOURS], BLANK())

In the Power BI table, you can place the CumulativeHours and SumUntil50Hours measures alongside your original data. You can also use conditional formatting to highlight the rows where the cumulative sum reaches or exceeds 50 hours by applying a rule based on the Flag50Hours measure.

For example, if your dataset includes the following values for hours: 24, 16, 16, 14, 16, 10, 20, 85, and 3, the cumulative sum reaches 50 hours within the third row. Therefore, the sum is adjusted to stop at 50, and subsequent rows reflect this change. Below is an example dataset with the calculated columns:

SumUntil50Hours = 
VAR Cumulative = 
    CALCULATE(
        SUM('Table'[TOTAL_W/O_HOURS]),
        FILTER(
            'Table',
            'Table'[Index] <= EARLIER('Table'[Index])
        )
    )
VAR RemainingTo50 = 50 - CALCULATE(
    SUM('Table'[TOTAL_W/O_HOURS]),
    FILTER(
        'Table',
        'Table'[Index] < EARLIER('Table'[Index])
    )
)
RETURN
IF(
    Cumulative > 50,
    IF(RemainingTo50 > 0, RemainingTo50, BLANK()),
    'Table'[TOTAL_W/O_HOURS]
)

This calculated column stops at 50.  

DataNinja777_0-1736659587447.png

 

 

In the Power BI table, you can place the CumulativeHours and SumUntil50Hours measures alongside your original data. You can also use conditional formatting to highlight the rows where the cumulative sum reaches or exceeds 50 hours by applying a rule based on the Flag50Hours measure.

For example, if your dataset includes the following values for hours: 24, 16, 16, 14, 16, 10, 20, 85, and 3, the cumulative sum reaches 50 hours within the third row. Therefore, the sum is adjusted to stop at 50, and subsequent rows reflect this change. 

 

DataNinja777_1-1736659790825.png

I have attached an example pbix file or your reference. 

 

Best regards,

 

View solution in original post

1 REPLY 1
DataNinja777
Super User
Super User

Hi @TI_25 ,

 

To dynamically calculate the sum of hours and indicate the point where a cumulative total reaches 50 hours, you can achieve this in Power BI using DAX formulas. The process involves creating a measure to calculate a cumulative sum and another measure to determine the sum until it reaches 50 hours.

Start by creating a measure for the cumulative sum of hours. You can use the SUMX function combined with FILTER to ensure that each row accumulates the sum of all previous rows:

CumulativeHours = 
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Index] <= MAX('Table'[Index])
    ),
    'Table'[TOTAL_W/O_HOURS]
)

Next, create a measure to flag rows where the cumulative sum reaches or exceeds 50 hours. This helps identify the point where the threshold is crossed. The formula for this measure is:

Flag50Hours = 
IF([CumulativeHours] >= 50, 1, 0)

To show the sum until the cumulative total reaches 50 hours, you need a measure that dynamically calculates and stops adding values once the cumulative sum hits 50 hours. The formula for this measure is:

SumUntil50Hours = 
IF([CumulativeHours] <= 50, [TOTAL_W/O_HOURS], BLANK())

In the Power BI table, you can place the CumulativeHours and SumUntil50Hours measures alongside your original data. You can also use conditional formatting to highlight the rows where the cumulative sum reaches or exceeds 50 hours by applying a rule based on the Flag50Hours measure.

For example, if your dataset includes the following values for hours: 24, 16, 16, 14, 16, 10, 20, 85, and 3, the cumulative sum reaches 50 hours within the third row. Therefore, the sum is adjusted to stop at 50, and subsequent rows reflect this change. Below is an example dataset with the calculated columns:

SumUntil50Hours = 
VAR Cumulative = 
    CALCULATE(
        SUM('Table'[TOTAL_W/O_HOURS]),
        FILTER(
            'Table',
            'Table'[Index] <= EARLIER('Table'[Index])
        )
    )
VAR RemainingTo50 = 50 - CALCULATE(
    SUM('Table'[TOTAL_W/O_HOURS]),
    FILTER(
        'Table',
        'Table'[Index] < EARLIER('Table'[Index])
    )
)
RETURN
IF(
    Cumulative > 50,
    IF(RemainingTo50 > 0, RemainingTo50, BLANK()),
    'Table'[TOTAL_W/O_HOURS]
)

This calculated column stops at 50.  

DataNinja777_0-1736659587447.png

 

 

In the Power BI table, you can place the CumulativeHours and SumUntil50Hours measures alongside your original data. You can also use conditional formatting to highlight the rows where the cumulative sum reaches or exceeds 50 hours by applying a rule based on the Flag50Hours measure.

For example, if your dataset includes the following values for hours: 24, 16, 16, 14, 16, 10, 20, 85, and 3, the cumulative sum reaches 50 hours within the third row. Therefore, the sum is adjusted to stop at 50, and subsequent rows reflect this change. 

 

DataNinja777_1-1736659790825.png

I have attached an example pbix file or your reference. 

 

Best regards,

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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