Matrix visual - summarize sub level of row in Total

[navafolk]

Hi pros,

I have a data 'Table'

My normal matrix visual has 2 levels in rows: 'Version_date' -> 'Product'

Please help if there is any way to summurize by product in the total without creating separate matrix for product performance, some thing looks like

Thank you all.

[v-dineshya]

Hi @navafolk ,

Thank you for reaching out to the Microsoft Fabric Community forum.

If @danextian  response is helpful, please “Accept it as a solution”  to assist other community members in resolving similar issues more efficiently.
Thank you.

[m4ni]

Hi @navafolk 

I dont think thats natively possible but you could simulate an effect by amending the table properties of two independant tables.  Hide totals and align text as needed gives you something like below...

Having 2 tables is a challenge though but visually can make it look like this.

[danextian · Accepted solution]

Hi @navafolk 

Create a separate dimensions table that has the version date as well as a row for the word total. You will be combining dates and text in one column so the result is a text thus the date must be formatted to its text equivalent.

VersionDates = 
VAR _dates =
    DISTINCT (
        SELECTCOLUMNS (
            'Table',
            "Version date", FORMAT ( 'Table'[Version_date], "dd mmmm yyyy" ),
            "Sort", 'Table'[Version_date]
        )
    )
VAR _total =
    DATATABLE ( "Version Date", STRING, "Sort", INTEGER, { { "Total", 10000000 } } )
RETURN
    UNION ( _dates, _total )

Create a one-to-many single direction relationship from VersionDates[Sort] to 'Table'[Date]

Create this measure:

Amount with Total Breakdown = 
IF (
    SELECTEDVALUE ( VersionDates[Version date] ) = "Total",
    CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS ( VersionDates ) ),
    SUM ( 'Table'[Amount] )
)

Show/hide the subtotal rows/columns as necessary

Please see the attached pbix.

[navafolk]

Super @danextian, a brilliant way, save my life.

[v-dineshya]

Hi @danextian ,

Thank you for posting response in community.

Regards,

Dinesh