Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AndersKa
Regular Visitor

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

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:

 

AndersKa_1-1734104599788.png

 

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.

 

AndersKa_0-1734104233865.png

 

Is that possible? Many thanks for your help in advance.

3 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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.

View solution in original post

mark_endicott
Super User
Super User

@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 

View solution in original post

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

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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
Regular Visitor

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

AndersKa_0-1734428142633.png

 

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

mark_endicott
Super User
Super User

@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
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.