Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
thomma
Frequent Visitor

Building matrix/table using measures

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.

 

thomma_1-1713184912335.png

 

 

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

 

Thanks in advance!


1 ACCEPTED SOLUTION
Joe_Barry
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


View solution in original post

4 REPLIES 4
AnalyticPulse
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


Hi @Joe_Barry ,

Works great, thanks for the detailed instruction!

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.