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.
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.
I need a measure that can
I am struggling to get the correct sum.
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |