Forum Discussion
Sum Total of Summarized Max Values by ID and State In Table
I have a metric called 'Total Hours', which is the total hours a worker (ID) has available by state. I summarized total hours as the maximum number of hours. I did this because they can have the same number of hours available in multiple states, so I didn't want to sum duplicate values. For example, 'A' has 2.0 hours of remote work availability in HI, MA, and NC.
I would like to sum those max values in the total of my table so I can show the total number of hours for all IDs in that state. I'm having trouble getting Power BI to show the correct total. I have tried creating a measure and summing the max for the id. I have also tried to just create a max column and not use the summarize feature.
| A | HI | 2.0 |
| B | HI | 1.5 |
| C | HI | .5 |
Total Max Hours =
SUMX(
VALUES(Table[Worker_ID]),
CALCULATE(MAX(Table[Total Hours]))
)
Where:
VALUES(Table[Worker_ID]): This function returns a unique list of IDs from your data.
MAX(Table[Total Hours]): This calculates the maximum total hours for each worker (ID).
SUMX: This function iterates over the unique IDs, summing the maximum value of total hours for each ID.
If this helps, please give a Like.
Thanks!
Keep Exploring
1 Reply
Total Max Hours =
SUMX(
VALUES(Table[Worker_ID]),
CALCULATE(MAX(Table[Total Hours]))
)
Where:
VALUES(Table[Worker_ID]): This function returns a unique list of IDs from your data.
MAX(Table[Total Hours]): This calculates the maximum total hours for each worker (ID).
SUMX: This function iterates over the unique IDs, summing the maximum value of total hours for each ID.
If this helps, please give a Like.
Thanks!
Keep Exploring