cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Avoid duplicating a column that will have the same result in a matrix

 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%

3 ACCEPTED SOLUTIONS
Super User
Super User

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

Solution Sage

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.

8 REPLIES 8
Super User

Helper I

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.

Thanks.

Super User

pls try

Helper I

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.

Solution Sage

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.

Helper I

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!

Super User

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

Helper I

Wow.  Thank you very much for taking the time to produce this.  Very interesting and informative  and much appreciated.