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

[PBIM]

	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%

[Ahmedx · Accepted solution]

pls try

[Ahmedx · Accepted 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

[Ahmedx]

Share sample pbix file to help you.

[PBIM]

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.

[Ahmedx · Accepted solution]

pls try

[PBIM]

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. 

[mahenkj2 · 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:

 https://community.fabric.microsoft.com/t5/Desktop/Creating-a-Matrix-with-a-static-column/td-p/311471... 

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.

[PBIM]

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!

[Ahmedx · Accepted 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

[PBIM]

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