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.
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.
Solved! Go to 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
)
Proud to be a Super User! | |
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
)
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |