cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Matrix visualization with rows calculated based on other calculated rows

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.

1 ACCEPTED SOLUTION
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!

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!