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

Get 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

Reply
PBIM
Helper I
Helper I

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

 Cat1  Cat2  Cat3  
 ExpectedReceivedPerCentExpectedReceivedPerCentExpectedReceivedPerCent
a10880%10770%1010100%
b12975%12758%121192%
c10990%10660%10990%
d12975%12650%121192%

 

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 
 ExpectedReceivedPerCentReceivedPerCentReceivedPerCent
a10880%770%10100%
b12975%758%1192%
c10990%660%990%
d12975%650%1192%

 

 

 

3 ACCEPTED SOLUTIONS

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

Screenshot_1.pngScreenshot_2.png
--------------

Sample PBIX file attached

 

View solution in original post

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:

mahenkj2_0-1699377226347.png

 

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:

mahenkj2_1-1699377343557.png

 

Hope it helps.

View solution in original post

8 REPLIES 8
Ahmedx
Super User
Super User

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.

PBIM_0-1699288658455.png

 

pls try

 

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:

 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:

mahenkj2_0-1699377226347.png

 

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:

mahenkj2_1-1699377343557.png

 

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!

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

Screenshot_1.pngScreenshot_2.png
--------------

Sample PBIX file attached

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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