Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Reeder
New Member

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
jgeddes
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...

jgeddes_0-1713818184984.png

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
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
jgeddes
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...

jgeddes_0-1713818184984.png

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
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors