Matrix column based on other matrix columns

[Anonymous]

Hi!

I am moving reports from excel to Power BI, and have encountered this problem.

This is how my data look like:

I arrange a matrix as following:

Now I want to add one more column which is based on the other two columns, like this:

If I create a measure of income 2020 - income 2019, the calculations are correct but it is not possible to add it as a column.

Anyone have an idea of how to do it? In excel, I would create a pivot table, and then create a new nice table which refers to the pivot table and simply add a column which subtracts the two columns. I have tried creating an additional matrix with measures on the rows, but it doesn't seems to be the most efficient way + it doesn't look so nice.

[v-yanjiang-msft]

Hi @Anonymous ,

Has @PaulDBrown 's solution solved your problem? If yes, please kindly accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ kalyj

[parry2k]

@Anonymous here you go, you have a solution. As I mentioned earlier, not as straightforward but a workaround. You have to be very specific to your requirements to make it work since the measure is tightly knitted, if you drift from your requirement, you have to come back and revisit your measures.

One way to make it simple is to use calculation groups which will make the repeated calculation easier. Cheers!!

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Anonymous unfortunately there is no straightforward solution to this, it will show diff measures as row since you have turned on show on rows (looks like)

There are workaround available, you can do a google search and will find some solution.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k  Thank you for your reply.

So far, I have not found a solution by googling. And I have googled on this for many hours.

Please, if you can find solution, let me know.

[PaulDBrown]

Here is one way. 

1) create a new table as the layout for the matrix columns. In my example, I've used the following code  in the new table option under modeling:

Matrix columns =
DISTINCT ( UNION ( VALUES ( 'Table'[Year] ), { "Diff" } ) )

I've added an index column for sorting purposes

 2) Create a relationship one-to-many between the 2 tables via the "Year" fields

3) Using simple base measures for the sum of income and cost, create the following tto use in the visual:

Icome (visual) =
VAR MXYear =
    CALCULATE (
        MAX ( 'Matrix columns'[Index] ),
        ALLSELECTED ( 'Matrix columns'[Year] )
    )
VAR MNYear =
    CALCULATE (
        MIN ( 'Matrix columns'[Index] ),
        ALLSELECTED ( 'Matrix columns'[Year] )
    )
VAR MXValue =
    CALCULATE (
        [Sum Icome],
        FILTER ( ALL ( 'Matrix columns' ), 'Matrix columns'[Index] = MXYear )
    )
VAR MNValue =
    CALCULATE (
        [Sum Icome],
        FILTER ( ALL ( 'Matrix columns' ), 'Matrix columns'[Index] = MNYear )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Matrix columns'[Year] ) = "Diff",
        MXValue - MNValue,
        [Sum Icome]
    )

Cost (visual) =
VAR MXYear =
    CALCULATE (
        MAX ( 'Matrix columns'[Index] ),
        ALLSELECTED ( 'Matrix columns'[Year] )
    )
VAR MNYear =
    CALCULATE (
        MIN ( 'Matrix columns'[Index] ),
        ALLSELECTED ( 'Matrix columns'[Year] )
    )
VAR MXValue =
    CALCULATE (
        [Sum Cost],
        FILTER ( ALL ( 'Matrix columns' ), 'Matrix columns'[Index] = MXYear )
    )
VAR MNValue =
    CALCULATE (
        [Sum Cost],
        FILTER ( ALL ( 'Matrix columns' ), 'Matrix columns'[Index] = MNYear )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Matrix columns'[Year] ) = "Diff",
        MXValue - MNValue,
        [Sum Cost]
    )

4) create the matrix using the measures on rows and the Year field from the Matrix layout table to get:

I've attached the sample PBIX file for you