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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PowerBI Matrix Data Type/Format Issue

Hi All,

 

I have a question on using a Power BI Matrix. I pasted a simplified example of my dataset below, and I basically want to lay it out in a matrix in a tax return-like format. The issue I'm running into is that there are percentages mixed with whole numbers in a single column, is there a way to show certain lines as percentages and others as whole numbers? I figured out the custom sorting so that's not an issue.

 

I've tried taking the unique names (Federal TI, State Mods, etc.) and making separate columns for each of them but that format doesn't work with a matrix because the values create individual column headers in the matrix. In Tableau, I would be able to drop the "measure names" (calculated column names) into the rows, and have their values show up in a single column (maintaining their datatypes), but I can't figure out how to do that in Power BI.

 

Any help would be appreciated.

Thanks!

 

Data Table:

EntityFiscal YearStateNameValue
Company A12/31/2017ALFederal TI100000
Company A12/31/2017ALState Mods-50000
Company A12/31/2017ALState TI50000
Company A12/31/2017ALAppt %0.5
Company A12/31/2017ALState Appt. TI25000
Company A12/31/2017ALTax Rate0.065
Company A12/31/2017ALIncome Tax1625
Company B12/31/2017ALFederal TI50000
Company B12/31/2017ALState Mods-50000
Company B12/31/2017ALState TI0
Company B12/31/2017ALAppt %0.5
Company B12/31/2017ALState Appt. TI0
Company B12/31/2017ALTax Rate0.065
Company B12/31/2017ALIncome Tax0

 

Desired Matrix:

EntityCompany ACompany B
Fiscal Year12/31/201712/31/2017
NameALAL
Federal TI         100,000         50,000
State Mods         (50,000)       (50,000)
State TI           50,000                     -  
Appt %50.00%50.00%
State Appt. TI           25,000                     -  
Tax Rate6.50%6.50%
Income Tax               1,625                     -  
1 ACCEPTED SOLUTION
AkhilAshok
Solution Sage
Solution Sage

It can be easily done with the following steps:

1. Do a Pivot Transoformation of the Name column in Power Query Editor, giving Value as the Values column.

2. Create a cross tab adding the respective items. Make sure to enable options Values->Show on rows

 

image.png

View solution in original post

2 REPLIES 2
AkhilAshok
Solution Sage
Solution Sage

It can be easily done with the following steps:

1. Do a Pivot Transoformation of the Name column in Power Query Editor, giving Value as the Values column.

2. Create a cross tab adding the respective items. Make sure to enable options Values->Show on rows

 

image.png

Anonymous
Not applicable

@AkhilAshok The only problem with the solution you provided is that example that I gave is extremely simplified. I have over 150 unique values in the Name column, so pivoting on the names colum makes the dataset very cumbersome.

 

But the show value on rows step worked like a charm! So simple... yet not so obvious! Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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