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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gforssberg
Helper I
Helper I

Help building a Table consisting only of Measures?

I have a series of measures that I've used to sum up totals from a table. See below for the gist of what it is. 

  • First I'm looking to ensure this was a correct usage of measures (from what I've read it is) vs. using Summarize or some other DAX function. The issue is that the filter contexts are different (won vs. open opportunities) AND I have to add totals together for some of the rows. I have to think measures are the only way? 
  • second, I need to visualize these measures in a table per a customers request. I tried using the Table Constructor but the filters on the report page don't seem to apply to it. How might go about creating a table/matrix for my need? 

 

 Total% to Goal Difference
Won Business (filter context:won) [Measure A][Measure A]/[Measure Goal][Measure Goal]-[Measure A]
Won + Commit (filter context:open) [Measure B][Measure B]/[Measure Goal][Measure Goal]-[Measure B]
Won + Commit + Upside (filter context:open)[Measure C][Measure C]/[Measure Goal][Measure Goal]-[Measure C]
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@gforssberg 

Here is one way.

First create two tables with the names of the rows and columns; we will use these to create the matrix:

 

 

Columns Names.JPG

 

Rows names.JPG

 Create the measures for each of the cells in the matrix. Create the final measure for the matrix which follows this logic:

Matrix Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won Business],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won + Commit],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won + Commit + Upside],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% won vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% w + c vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% w + c+ u vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [Won Business dif goal],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [won + commit diff goal],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [w + c+ u diff goal],
    BLANK ()
)

Add the column from the rows table to the rows bucket in the matrix, add the column from the column table to the column bucket and finally add the matrix measure.

result.JPG

 

 I've attached the PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

16 REPLIES 16
PaulDBrown
Community Champion
Community Champion

@gforssberg 

Here is one way.

First create two tables with the names of the rows and columns; we will use these to create the matrix:

 

 

Columns Names.JPG

 

Rows names.JPG

 Create the measures for each of the cells in the matrix. Create the final measure for the matrix which follows this logic:

Matrix Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won Business],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won + Commit],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Total", [Won + Commit + Upside],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% won vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% w + c vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "% of Goal", FORMAT ( [% w + c+ u vs goal], "###%" ),
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won Business"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [Won Business dif goal],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [won + commit diff goal],
    SELECTEDVALUE ( RowTable[RowsNames] ) = "Won + Commit + Upside"
        && SELECTEDVALUE ( ColumnsTable[ColumnNames] ) = "Difference", [w + c+ u diff goal],
    BLANK ()
)

Add the column from the rows table to the rows bucket in the matrix, add the column from the column table to the column bucket and finally add the matrix measure.

result.JPG

 

 I've attached the PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






akhaliq7
Continued Contributor
Continued Contributor

Thanks this solution has been really helpful also I had never thought of creating a matrix measure like in this solution.

I'm kind of at a loss because the diagonal means that the columns and rows references all work in the IF/AND of switch (as I understand how switch works), and i tried swapping measures that weren't working into the columns and rows that were and the measure worked. So i'm not sure what the heck is going on... 

@gforssberg 

It's probably either:

1) the rogue measures aren't filtering as needed (since each cell in the matrix contains a unique measure). So try checking each one individually outside the switch context.

2) the names in the SELECTEDVALUE expressions aren't the exact column/row definition. This you can easily rule out by swapping out the names of the rows/columns by the equivalent "order" value (just beware that if the values are integers, you need to type in 1 (or 2 or 3) and not "1" which you would use should the field be formatted as text.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I figured it out! Although, how would I format the background of a couple of the measures? I tried a nested switch but didn't seem to work. 

@gforssberg 

Can you provide an example of what criteria defines the formatting?

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






0-80% is red, 80-100% is yellow, 100+ is green. Thanks!

 

formatting.png

@gforssberg 

That's actually quite tricky since the "% to Goal" field is formatted as text and VALUE doesn´t seem to convert it to a number. However, where there's a will there's a way:

 

Conditional Formatting % vs Goal" = 
VAR transform = SWITCH(TRUE(),
SELECTEDVALUE(ColumnsTable[Order]) = "2" && SELECTEDVALUE(RowTable[Order]) = "1", [% won vs goal],
SELECTEDVALUE(ColumnsTable[Order]) = "2" && SELECTEDVALUE(RowTable[Order]) = "2", [% w + c vs goal],
SELECTEDVALUE(ColumnsTable[Order]) = "2" && SELECTEDVALUE(RowTable[Order]) = "3", [% w + c+ u vs goal],
"Na")
RETURN
SWITCH(TRUE(),
RIGHT([Matrix Measure], 1) <> "%", 0,
transform < 0.8, 1,
transform >= 1 , 3,
2)

 

Select the [matrix measure] under Conditional formatting in the formatting pane and set the parameters as follows:

Conditional formatting.JPG(I also applied the conditional formatting to the font colour)

And you will see:Condit Form result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown I think this has legs BUT after trying to mimic everything I get this. It has to mean something. I did something wrong I know...

 

 

Tried to mimic.png

parry2k
Super User
Super User

@gforssberg If you have all the measures (seems like you do), put on values in matrix visual, go to format pane and turn on show on rows and I think that is what you are looking for.

 

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

When put on show as rows it shows as one column. I want to have the percentages as a second column. All part of one matrix. Below is what it shows as with show as rows

show as rows.png

but I want it to look like this: 

 

this.png

 

parry2k
Super User
Super User

@gforssberg I'm still not sure why you cannot put it in a table /matrix visual. what is the issue? I'm still not clear about the problem. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k i'll distill it down a bit, perhaps this will help. The data is pretty sensitive and it would take some time to blind. 

So, I just need to have multiple columns in a matrix made of the measures I referenced. As it is, I can multiple columns OR multiple rows, because they are only values. Does that make sense? The first figure shows where I'm at now. The second one below show what I desire. 

Distill.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

What I'm looking to reproduce

Part Twooo.png

gforssberg
Helper I
Helper I

Any ideas? Thanks!

gforssberg
Helper I
Helper I

See below for an example of the core table, lets call it 'Opportunities'. 

 

There are four pertinent columns: [Deal Stage],  [Forecast], and then Actual Revenue or Estimated Revenue, depending on whether the opportunity is won or open. I want totals like so: 

 

  • Won
  • Won + Commit 
  • Won + Commit 
  • Won + Commit + Upside 

Example.jpg

What  I had done (and forgive typos, I retyped it here) was create a measure for each, like: 

 

Win Total = 
CALCULATE ( 
  SUM ( 'Opportunities'[ActualRevenue]), 
   'Opportunities'[Deal Stage] IN {"Won"}
)
  

 

 

and then 

 

Commits = 
CALCULATE( 
SUM(Opportunities'[EstimatedValue]) FILTER('Opportunities',[Deal Stage]="Open") && ([Forecast]="2. Commit")))

 

 

Then I just added the measures together like 

 

[Win Totals] + [Commits]

 

 

The issue was that I can't put this in a table very easy (see table above for what I want the visual table to look like) since its all measures. I was wondering whether the measures were best practice and if so how to lay them out in a table/matrix. Thanks!

parry2k
Super User
Super User

@gforssberg It would be much easier if you throw some sample data (maybe pbix ) file and share the expected output. It will help to provide the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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