Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add 2nd column header to Matrix

Greetings. Dealing with budgets and actuals.   I've got a user that requested a matrix that shows 12 months of data. If actuals are available the column displays actuals. If the actuals are not ava...
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    I've edited this post since the model works with an active relationship between the tables

    Sure, here goes.

    1) You need to create a table which will allow for the column structure of the matrix. This you can create in Power Query which will furthermore make it dynamic. Basically you need a table referenced to the fact table and add a conditional column which returns "Budget" if the row for "Actuals" is blank else "Actuals"

    This is the M-code I've used

     

    let
        Source = Table,
        #"Removed Columns" = Table.RemoveColumns(Source,{"dimension", "Budget"}),
        #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Calculation", each if [Actuals] = null then "Budget" else "Actuals"),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Actuals"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns1")
    in
        #"Removed Duplicates"

     

    to get

     

    2) Set up a relationship between the month fields:

     3) create simple base measures for actuals and budget

     

    Sum of Actuals = 
    SUM(Table[Actuals])
    Sum of Budget = 
    SUM(Table[Budget])

     

    4) Create the final measure you will be using in the matrix

     

    Final matrix measure =
    SWITCH (
        SELECTEDVALUE ( 'Matrix Header'[Calculation] ),
        "Actuals", [Sum of Actuals],
        "Budget", [Sum of Budget,
        SUMX ( 'Table', IF ( ISBLANK ( [Sum Actuals] ), [Sum Budget], [Sum Actuals] ) )
    )
    

     

    5) finally set up the matrix visual using the whatever field for the rows, the fields from the 'Matrix Header' table as columns and [Final matrix measure] as the values. Format the visual to your liking

     

    I've attached the sample PBIX file for you