topic Re: Sum selected columns based on lookup of column names in other table in DAX Commands and Tips

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

AndersKa — Fri, 13 Dec 2024 15:46:38 GMT

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.

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

johnt75 — Fri, 13 Dec 2024 15:53:15 GMT

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.

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

mark_endicott — Fri, 13 Dec 2024 17:19:15 GMT

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

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

AndersKa — Tue, 17 Dec 2024 09:41:40 GMT

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

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

johnt75 — Tue, 17 Dec 2024 09:58:13 GMT

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

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

mark_endicott — Tue, 17 Dec 2024 10:07:41 GMT

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 )

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

AndersKa — Tue, 17 Dec 2024 14:06:08 GMT

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