Building matrix/table using measures

[thomma]

Hi, 

I am currently working on creating a visual that has the same indentation structure and appearance as the Excel table below. Unfortunately, achieving this with the standard table or matrix visual in Power BI is not possible as far as I know.

Note: the values in de columns are measures from different tables.

Thanks in advance!

[AnalyticPulse]

it is possible if you use proper dax for every column, usinng  functions like ALL, ALLExcept, Filter, calculate  , you can achieve this, pretty easy if you know the filter conditions properly.

[Kishore_KVN]

Hello @thomma ,

You can use ROW function to combine multiple measure and make a single table using UNION overall syntax looks as below:

Firstly Go to modelling in Power BI Desktop and click on New Table
Then give some name for table and its DAX calculation should look as below:

Table_Name = 
Union(
ROW(.........................................................),

ROW(.........................................................),

)

You can use this in a Matrix visual. 

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

[Joe_Barry · Accepted solution]

Hi @thomma 

You can acheive it, but it will take some modelling and creating measures.

• In the ribbon, click on Enter Data and create a Column with all the Values in column A of your screenshot
• Add a second column, we want to be able to sort the columns correctly, so give column A a numerical value so in your case for each row 1- 7
• Load the table and go to table view
• Highlight column A and go to the ribbon and go to Column tools > Sort by column and choose the numercial column
• You will then need to create sperate measures for each Row

Contracted Projects =
CALCULATE(
     DISTINCTCOUNT('Project'[ID]), 
       KEEPFILTERS ('Project'[Type] = "Contracted")

Repeat the measures for each row then create this measure

Matrix View =
SWITCH (
    SELECTEDVALUE ( 'Project Matrix'[LineItem Desc] ),
    "Contracted projects", [Contracted Projects],
    "Already invoiced", [Already Invoiced],
    "To Invoice", [To Invoice],
    "Prospects (100%)", [Prospects - 100%),
    "Prospects (75%)", [Prospects - 100%),
    "Total prospects", [Total Prospects],
    "Estimated Revenue", [Estimated Revenue])

This will show everything in one row, to acheive what you have in the Screenshot, you will need to create two further measures in the same format, but remove To invoice, total prospects and estimated revenue from the first emasure. In the second measure, just add To invoice, total prospects and in the thrird measure just add Estimated Revenue

Make sure that the Text in the measure matches that of your generated table

Thanks

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[thomma]

Hi @Joe_Barry ,

Works great, thanks for the detailed instruction!