Matrix visualization with rows calculated based on other calculated rows

[Reeder]

I'm trying to make a report that's like a pivot table where:
- The columns are the dates from the raw data
- The first row is a running total of amount spent per day
- The second row is a running total of units per day
- The third row is the value from the first row divided by the value from the second row (for each column)

The query that selects the underlying data looks something like:

 (
     select date_day, '01 spend amount' as rowname, amount_spent as value
     from table1
 )
 union
 (
     select date_day, '02 # of units' as rowname, num_units as value
     from table2
 )

Some sample data may look like this:

date_day	rowname	value
'2/8/2024'	'01 spend amount'	6596.96
'2/8/2024'	'01 spend amount'	0
'2/7/2024'	'01 spend amount'	165.25
'2/8/2024'	'01 spend amount'	155.76
'2/7/2024'	'01 spend amount'	0
'2/7/2024'	'01 spend amount'	4101.55
'2/7/2024'	'02 # of units'	53
'2/7/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	70
'2/8/2024'	'02 # of units'	5
'2/8/2024'	'02 # of units'	107
'2/8/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	40
'2/8/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	1
'2/8/2024'	'02 # of units'	2
'2/8/2024'	'02 # of units'	3
'2/8/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	1
'2/7/2024'	'02 # of units'	2
'2/7/2024'	'02 # of units'	2

So this way, I can group and filter by date (tables 1 and 2 may also have other filters applied), get the aggregate value for each day, and use "rowname" to define the rows in the matrix visualization.

My problem is I can't seem to figure out a way to compute an additional row based on values from the other two rows, while those rows show the running total instead of the value per day.

Essentially, I'm trying to make a row of values that are calculated by dividing the running total of one row with the running total of another row.

[jgeddes · Accepted solution]

With the starting data as 

date_day	rowname	value
2/8/2024	spend	6596.96
2/8/2024	spend	0
2/7/2024	spend	165.25
2/8/2024	spend	155.76
2/7/2024	spend	0
2/7/2024	spend	4101.55
2/7/2024	units	53
2/7/2024	units	1
2/8/2024	units	1
2/8/2024	units	1
2/7/2024	units	70
2/8/2024	units	5
2/8/2024	units	107
2/8/2024	units	1
2/7/2024	units	1
2/8/2024	units	1
2/8/2024	units	40
2/8/2024	units	1
2/7/2024	units	1
2/7/2024	units	1
2/8/2024	units	1
2/8/2024	units	1
2/8/2024	units	1
2/8/2024	units	1
2/8/2024	units	2
2/8/2024	units	3
2/8/2024	units	1
2/7/2024	units	1
2/7/2024	units	2
2/7/2024	units	2

You can get...

with the three measures

Amount to Date = 
SUMX(
    FILTER(ALL('Table'), 'Table'[date_day] <= MAX('Table'[date_day]) && 'Table'[rowname] = "spend"),
    'Table'[value]
)

Units to Date = 
SUMX(
    FILTER(ALL('Table'), 'Table'[date_day] <= MAX('Table'[date_day]) && 'Table'[rowname] = "units"),
    'Table'[value]
)

Amount to Date / Units to Date = 
DIVIDE(
    [Amount to Date],
    [Units to Date],
    0
)