Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Cat1 | Cat2 | Cat3 | |||||||
Expected | Received | PerCent | Expected | Received | PerCent | Expected | Received | PerCent | |
a | 10 | 8 | 80% | 10 | 7 | 70% | 10 | 10 | 100% |
b | 12 | 9 | 75% | 12 | 7 | 58% | 12 | 11 | 92% |
c | 10 | 9 | 90% | 10 | 6 | 60% | 10 | 9 | 90% |
d | 12 | 9 | 75% | 12 | 6 | 50% | 12 | 11 | 92% |
I have created a matrix from measures per above. Works fine except that, as 'Expected' is a function of the row (a, b, c, d), it's the same for every column category. Please, is there a way, I can get to only show once, as per below.
Thanks in advance for any insight.
Cat1 | Cat2 | Cat3 | |||||
Expected | Received | PerCent | Received | PerCent | Received | PerCent | |
a | 10 | 8 | 80% | 7 | 70% | 10 | 100% |
b | 12 | 9 | 75% | 7 | 58% | 11 | 92% |
c | 10 | 9 | 90% | 6 | 60% | 9 | 90% |
d | 12 | 9 | 75% | 6 | 50% | 11 | 92% |
Solved! Go to Solution.
yes you can but it's difficult
1) you need to create an unrelated table (category + name of measures)
2) create suitable measures
3) create one measure with a dynamic format
--------------
Sample PBIX file attached
Hi @PBIM ,
Your curosity for an alternate solution took me to search for it.
I found a link here which is the first step to see what's our aim could be:
As per this, our static column should be in the 'Expected table'.
Then I created a calculated column in the table:
Expected =
CALCULATE(countrows(tblExpected), ALLEXCEPT(tblExpected, tblExpected[Row], tblExpected[Cat]))
Then your table look like this:
Use this new column expected in matrix with some formating changes as suggested in above link. Now you do not need to measure 'Expect_for_Row'.
Final visual output is like below:
Hope it helps.
Share sample pbix file to help you.
OK. Took me quite a while because I had to remove identification data but the linked file and image below hopefully demonstrates my issue. I just want the expected column once and then the received and percentage received for each category i.e. 1 + 2 x 3 = 7 columns rather than 3 x 3 = 9 columns, as expected is the same for all categories.
My pbix is at https://drive.google.com/file/d/1nM_tWL7cYP9WoZpP4ba6DX9HejLlA6vh/view?usp=sharing
Thanks.
Thank you. Yes, that works. I see that your stratergy was to create a measure for the first column only.
Out of interest - please don't take this the wrong way as I am happy with and grateful for your solution - is this the only way to do this? When I align column headers etc, in my finished product it will probably be apparent that there is an extra column for the first category only. That's not a big deal. I just wondered if there was, in principle, a way of adding an extra stand-alone column to a matrix (even if it is more agro than is justified for this small example).
Thanks anyway - I will mark your response as the accepted solution.
Hi @PBIM ,
Your curosity for an alternate solution took me to search for it.
I found a link here which is the first step to see what's our aim could be:
As per this, our static column should be in the 'Expected table'.
Then I created a calculated column in the table:
Expected =
CALCULATE(countrows(tblExpected), ALLEXCEPT(tblExpected, tblExpected[Row], tblExpected[Cat]))
Then your table look like this:
Use this new column expected in matrix with some formating changes as suggested in above link. Now you do not need to measure 'Expect_for_Row'.
Final visual output is like below:
Hope it helps.
Thank you for this. I will take time, over the weekend (when I have more headspace than in the office duging the week), to absorb this and Ahmedx's method!
Wow. Thank you very much for taking the time to produce this. Very interesting and informative and much appreciated.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |