Create complex table with measures

[Xilalus]

Hey Guys,

I am new to using measures in PowerBI and I have this table I created a while a ago, which I would like to recreate using measures.

Reasons for this being:

- Performance

- Better structure of the project

- Possibility to easily add stuff down the road

At the moment I use the Financial Reporting Matrix from Profitbase for this table, which relies on a data table with entries for the columns and rows to display the value at the correct position. Here is a model on how it looks:

Each row has it's own formula that calculates the value and at the moment I use a table for each row to calculate the values and then I use a union to put all of those entries into one single table which is then used in the table. Is is done both for IS and PLAN numbers.

Using Measures I think that I can just use a measure per row and then add the values of the measures for the intermediate values. This leaves me with two questions:
- How can I assign a measure per row? (Measure can only be added as Value which then displays the same value for the entire column)

- How can I have IS and PLAN numbers side by side like in the image above? (I have tried using a matrix which allows me to put the measures as rows, here I don't know how to create the distinction between IS and PLAN, example of this in the image below)

Here I also had to put a single empty value as the row to display the different measures.

I know this is quite complex and maybe difficult to understand, so if you need more context or other informations feel free to ask for them 🙂

Thanks for your time

[AntonioM · Accepted solution]

Hi @Xilalus,

This is definitely possible. It looks like you've already got most of the steps, just need to create some measures. 

Are IS and PLAN measures already? If they are, you can put both of those in the Values field (with measures not set to rows).

Which will give you the columns you need

If they are two values from a column (maybe a 'type' column that can be IS or PLAN) then you could put that column in the Columns Field instead, under the month and year.

You can use SWITCH and SELECTEDVALUE to find what row the measure is being calculated on and calculate a different value accordingly. For example, 

IS = 
SWITCH(
    SELECTEDVALUE(Headers[Row Header]),
    "A",
        "each",
    "B",
        "word",
    "C",
        "appears",
    "D",
        "a",
    "E",
        "different",
    "F",
        "row",
    "Intermediate Value 1", 
        17
)

PLAN = 
SWITCH(
    SELECTEDVALUE(Headers[Row Header]),
    "A",
        "plan",
    "B",
        "is",
    "C",
        "in",
    "D",
        "a",
    "E",
        "different",
    "F",
        "column",
    "Intermediate Value 1", 
        10
)

These two measures give you the table

Instead of the words I've added, you can have a different measure or calculation to evaluate on each row. One thing to note is if you have a mix of text and numbers like above, the entire column will be aligned as one type.

This won't give you a value for the Total, but you can either add an extra 'else' line into the SWITCH, for if there is no match to a row header, or create that row yourself.