Sum selected columns based on lookup of column names in other table

[AndersKa]

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.

[johnt75 · Accepted 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.

[mark_endicott · Accepted solution]

@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 

[johnt75]

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

[AndersKa]

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.

• With my new unpivoted datasets I now select "work in process" kanban statuses Analysis+Backlog+Implementing
• This in turn includes all potential issue-statuses I have mapped to these 3 kanban statuses
• Which in turn give me all unpivoted lines
• Now I'd like to distinctly count the number of issues (keys) with days summing up to less than 100 ...
• ... and divide by total number of issues (keys) ...
• ... to produce a percentage of issues done in less than 100 days

[mark_endicott · Accepted solution]

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

[AndersKa]

That is brilliant, works a charm. Thank you kindly.

[mark_endicott · Accepted solution]

@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 

[johnt75 · Accepted 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.