Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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] |
Solved! Go to Solution.
Here is one way.
First create two tables with the names of the rows and columns; we will use these to create the matrix:
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.
I've attached the PBIX file for your reference
Proud to be a Super User!
Paul on Linkedin.
Here is one way.
First create two tables with the names of the rows and columns; we will use these to create the matrix:
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.
I've attached the PBIX file for your reference
Proud to be a Super User!
Paul on Linkedin.
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...
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.
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.
Can you provide an example of what criteria defines the formatting?
Proud to be a Super User!
Paul on Linkedin.
0-80% is red, 80-100% is yellow, 100+ is green. Thanks!
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:
(I also applied the conditional formatting to the font colour)
And you will see:
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...
@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
but I want it to look like this:
@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.
What I'm looking to reproduce
Any ideas? Thanks!
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:
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!
@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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |