Matrix Challenges

[jmfillman]

The Matrix visual is something I am really struggling to understand in relation to my data, and after days of trying, I cannot get what I need. I have a Pro, not Enterprise account.

I have 4 tables (also see the first image):

1) Project Table

2) Plan Table (Many to One to Project Table)

3) Requested (Many to One to Plan Table)

4) Approved (Many to One to Plan Table)

Demonstrated in Excel, tables are in BI Dataflow with a Dataset and Report.

I am trying to achieve something like the below image and a Matrix seems the appropriate visual. I have been able to get something close (although still not quite), if I only use the Requested Plan table OR the Approved Plan table, but not together.

Been trying things for days and just not making progress. Any solutions or similar examples you might point me to would be appreciated.

[v-rzhou-msft · Accepted solution]

Hi @jmfillman ,

Here I suggest you to create three dim tables to help you calculating.

My Test:

DimGroup = FILTER(DISTINCT(UNION(VALUES('Approved Plan'[Group]),VALUES('Requested Plan'[Group]))),[Group]<>BLANK())

DimQuarter = FILTER(DISTINCT(UNION(VALUES('Approved Plan'[Fiscal Quarter]),VALUES('Requested Plan'[Fiscal Quarter]))),[Fiscal Quarter]<>BLANK())

DimYear = FILTER(DISTINCT(UNION(VALUES('Approved Plan'[Fiscal Year]),VALUES('Requested Plan'[Fiscal Year]))),[Fiscal Year]<>BLANK())

Measures:

Approved = 
CALCULATE (
    SUM ( 'Approved Plan'[Approved Hour] ),
    FILTER (
        'Approved Plan',
        'Approved Plan'[State] = "Approved"
            && 'Approved Plan'[Group] = MAX ( DimGroup[Group] )
            && 'Approved Plan'[Fiscal Year] = MAX ( DimYear[Fiscal Year] )
            && 'Approved Plan'[Fiscal Quarter] = MAX ( DimQuarter[Fiscal Quarter] )
    )
)

Planned = 
CALCULATE (
    SUM ( 'Requested Plan'[Planned Hour] ),
    FILTER (
        'Requested Plan',
        'Requested Plan'[State] = "Planned"
            && 'Requested Plan'[Group] = MAX ( DimGroup[Group] )
            && 'Requested Plan'[Fiscal Year] = MAX ( DimYear[Fiscal Year] )
            && 'Requested Plan'[Fiscal Quarter] = MAX ( DimQuarter[Fiscal Quarter] )
    )
)

Requested = 
CALCULATE (
    SUM ( 'Requested Plan'[Planned Hour] ),
    FILTER (
        'Requested Plan',
        'Requested Plan'[State] = "Requested"
            && 'Requested Plan'[Group] = MAX ( DimGroup[Group] )
            && 'Requested Plan'[Fiscal Year] = MAX ( DimYear[Fiscal Year] )
            && 'Requested Plan'[Fiscal Quarter] = MAX ( DimQuarter[Fiscal Quarter] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmfillman]

In your test file, this works pretty much as expected, but integration this into my existing report, I keep hitting a wall. All the dimension tables are showing the merged data, but even though my table relationships match, but as soon as I add any of the Dimension columns into the Matrx, it cannot find the relationship between fields, presumably up to the Project Table as that is the table for the first 2 Rows fields. Adding the 3rd row(any dimension table column), doesn't work.

I can't share the PBIX as it has internal data and connected internally and wouldn't connect anyway...

[jmfillman]

Wow, thank you! Going to have to play with this for a while, but certainly looks to be what I need or enough to ultimately get to what I need.