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
magnussoerensen
Frequent Visitor

How to sum only max values from each category in a measure that will be displayed in a matrix visual

My data has 3 levels of granularity. Each row is a registration of how much time was spent on each process. Some processes happen in parallel and will therefore have the same Process no. This data is sample data for a single Global Process of which there are multiple.

magnussoerensen_0-1676021234050.png

I need a measure that can

  1. Calculate the average time for each Process Name in a fact table
  2. Calculate the max value of these average times for each Process no.
  3. Calculate the sum of these max values for each Global Process

I am struggling to get the correct sum.

magnussoerensen_1-1676021655434.png

 

The Avg Time column is just the average and will of course only give the correct result on the Process Name level.

 

Avg Time = 
AVERAGE('Table'[Time])

 

My "best" attempt so far was to make two virtual tables, but I am not able to make a relationship between the two to get the right context. The second virtual table has a row for each Process no., but when I try to calculate the max value from the first virtual table I simply get the overall max value. This can be seen in the matrix visual where I get the correct value for each Process no. row since it only includes a single Process no. On the Global Process level where there are multiple Process no.'s I get the overall max times the number of Process no.'s: 7.5*5=37.5.

 

Sum of Max =
VAR AvgTimeTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Process Name],
            'Table'[Process no.],
            'Table'[Global Process]
        ),
        "Avg Time", CALCULATE ( AVERAGE ( 'Table'[Time] ) )
    )
VAR MaxTime =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Table', 'Table'[Process no.] ),
            "Max", CALCULATE ( MAXX ( AvgTimeTable, [Avg Time] ) )
        ),
        [Max]
    )
RETURN
    MaxTime

 

I am looking for a measure that will give me the correct sum: 5+3,67+7,5+6,2+3,67=26,04. The measure will be used in matrix visual where the rows are Global Process and Process Name.

 

The sample file can be downloaded from here: LINK 

 

0 REPLIES 0

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.