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.
Hi Community,
I am hoping someone can help me with summing selected columns based on a lookup of column names in another table.
Table 1 "Lead Time" contains jira issues and their respective "times in status" in a number of different columns, looks like this:
Now I want to create a calculated column (or a measure) that sum a selected number of columns.
The challenge is I would like to do it using a lookup in another table.
Table 2 "Status Mapping" below. So I'd like to create a Backlog column/measure in Table 1 that dynamically sum up the four columns that I have set as the Backlog kanban status below.
Is that possible? Many thanks for your help in advance.
Solved! Go to Solution.
Unpivot the columns so that you are left with the issue ID and any other columns you need plus a status type column and the time in that status. You can then link your status mapping table to the status type column.
@AndersKa - @johnt75's approach is correct.
Here's a blog of mine on why it makes things easier and is better for performance: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/
and
here's some guidance on how to do it: https://youtu.be/li0c6R6UpCw?si=cS381Negdvm50cpp&t=92
@AndersKa - This measure creates a table which sums all of the Days at the issue key level and then uses it as a basis for find the ones under 100 days.
I have not included any status filtering, but that can be done by wrapping the summarize in a CALCULATETABLE and using the filter section to adjust the status' you need:
VAR _table =
SUMMARIZE ( 'Table', 'Table'[key], "@totaldays", SUM ( 'Table'[days] ) )
VAR _less_than_100 =
COUNTROWS ( FILTER ( _table, [@totaldays] < 100 ) )
VAR _total =
DISTINCTCOUNT ( 'Table'[key] )
RETURN
DIVIDE ( _less_than_100, _total )
Try
Less than 100 days % =
VAR WorkInProgress =
ADDCOLUMNS (
DISTINCT ( 'Table'[Key] ),
"@hours",
CALCULATE (
SUM ( 'Table'[Days] ),
KEEPFILTERS ( 'Table'[Kanban status] IN { "Analysis", "Backlog", "Implementing" } )
)
)
VAR TotalKeys =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Key] ), REMOVEFILTERS () )
VAR Result =
DIVIDE ( WorkInProgress, TotalKeys )
RETURN
Result
Thank you both for your support.
With your advice I managed to go upstream and unpivot the columns at the source. So now that I got "normalized" data I stumbled into another challenge. Hmm... I suspect there is a fairly easy solution but my DAX skills aren't up to it yet. If you have the time I would love your input on this too.
I want to create a percentage of issues with a lead time of less than 100 days. With my pivoted data this was quite easy but now I struggle.
@AndersKa - This measure creates a table which sums all of the Days at the issue key level and then uses it as a basis for find the ones under 100 days.
I have not included any status filtering, but that can be done by wrapping the summarize in a CALCULATETABLE and using the filter section to adjust the status' you need:
VAR _table =
SUMMARIZE ( 'Table', 'Table'[key], "@totaldays", SUM ( 'Table'[days] ) )
VAR _less_than_100 =
COUNTROWS ( FILTER ( _table, [@totaldays] < 100 ) )
VAR _total =
DISTINCTCOUNT ( 'Table'[key] )
RETURN
DIVIDE ( _less_than_100, _total )
That is brilliant, works a charm. Thank you kindly.
@AndersKa - @johnt75's approach is correct.
Here's a blog of mine on why it makes things easier and is better for performance: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/
and
here's some guidance on how to do it: https://youtu.be/li0c6R6UpCw?si=cS381Negdvm50cpp&t=92
Unpivot the columns so that you are left with the issue ID and any other columns you need plus a status type column and the time in that status. You can then link your status mapping table to the status type column.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |