Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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.
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.
I have attached an example pbix file or your reference.
Best regards,
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.
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.
I have attached an example pbix file or your reference.
Best regards,
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
18 | |
7 | |
7 | |
5 | |
5 |
User | Count |
---|---|
25 | |
10 | |
10 | |
9 | |
6 |