Can't get Desired Matrix Table Rows Format

[julesdude]

Hi,

I have a matrix table in my report and I am trying to get it to display like this:

As you can see, Rows are ordered by "Asset Name and reference", "Purchase Price" and then "Last Valuation Value".
Data comes from three tables in the data model - one for asset info, one for asset purchase prices and one for asset valuations.  Whilst I have measures that find and filter both for the latest valuation of the asset and the asset purchase price, because they are measures i cannot drag these to the Rows section of the table. I don't think I can create a DAX column with the valuation data I need against the asset becuase in the report is an As Of Date date picker the user can select to see where things stood on a specific day and so valuations can be different depending on the date selected and when valuations took place historically:

Valuations table example:

Asset Reference	Valuation Date	Valuation Value
002	12/02/2023	320000000
002	10/01/2022	300000000
003	01/06/2022	200000000

I can place the measures in the Values section of the matrix table but doing this i get a repetition of the values on each row which looks messy - I only need it for the top row really and any further rows relating to the asset should be blank. So I don't want something like this:

The measure I am using for the last valuation is:

Valuation Amount (last) = 
VAR result = CALCULATE (
    SUM ( Asset_Valuation[Asset Value] ),
    LASTDATE ( Asset_Valuation[Valuation Date] )
)
RETURN

It's a similar syntax to get the Purchase Price value too.
Can this measure be adapted as a visual level filter in some way so that I can place the Valuation Amount field in the Rows section? Is what I am trying to achieve at all possible or a workaround to do this? Is there another 3rd party visual that can provide this flexibility?

[Anonymous]

Hi @julesdude ,

What can be put into the matrix's Rows and Columns field option can only be the fact field in the table and not the measures. Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards