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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Share sample pbix file to help you.

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.
My pbix is at https://drive.google.com/file/d/1nM_tWL7cYP9WoZpP4ba6DX9HejLlA6vh/view?usp=sharing

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.

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors