Forum Discussion
Calculate subtotal rows by column?
I have some data ('HSBY') which contains periods during which an application was unavailable.
The data is formatted with a Date colum, a start time column, a duration and an endtime column and I add the minute number (from 0-1440) in the day for the start and end times:
I'm trying to create a timeline chart, so I created a measure ('On HSBY') for each 15-minute time period in the day and set that to 0 ir 1 if the application was unavailable for all or part of that period:
On HSBY =
VAR TimeSlot = MAX('Timeslots'[Slot])
VAR TimeSlotSize = 15
VAR TimeStart = MAX('HSBY'[StartMinute])
VAR TimeEnd = MAX('HSBY'[EndMinute])
VAR InStartSlot = AND(TimeStart >= TimeSlot, (TimeSlot+TimeSlotSize) > TimeStart)
VAR InLastSlot = AND(TimeEnd >= TimeSlot, (TimeSlot+TimeSlotSize) > TimeEnd)
VAR InTimeRange = AND(TimeStart <= TimeSlot, TimeSlot < TimeEnd)
RETURN IF(InStartSlot + InLastSlot + InTimeRange > 0, 1, 0)
'Timeslots'[slot] is a simple table created from :
Timeslots = GENERATESERIES(0,1425,15)
to create the 15-minute time periods.
When displayed as a martix, with DATE, Start Time and End Time as the Rows, Slot as the colums and 'On HSBY' as the values, I can get the following:
which with a bit of conditional formatting becomes:
For the 15/05/21 for example, we have 2 separate rows which I'd like displayed as one, so I end up with have one row per day which I can then hopefully format.
How do I create another measure that is applied after my 'On HSBY' measure to create a new row per day with the sum of all time rows for that day?
The matrix would have to show only the daily subtotal rows.
4 Replies
Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.Thanks but I don;t think this will work - the 0s and 1s that I am formatting in my DAX measure and that are displayed inthe matric don't exists as values in a table, so I don't know if I can create a susb-total from them?
I'm still quite new to DAX and Power Bi, so I may be looking at this incorrectly.
I think one problem is that the data displayed in that matrix doesn't exist as a table anywhere -the cell contents are calculated dynamically (the measure 'On HSBY').
So to do this I need to somehow create an actual table either in Power BI using DAX or in Power Query when I pull the data in using M?
I've posted a new-but-related question, as I've managed to build a blank timeslot matric in Power Query, but am unsure of how to update each timeslot : https://community.fabric.microsoft.com/t5/Desktop/Update-a-table-with-values-from-another-table/m-p/3272037#M1096389
