Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Entity | Fiscal Year | State | Name | Value |
Company A | 12/31/2017 | AL | Federal TI | 100000 |
Company A | 12/31/2017 | AL | State Mods | -50000 |
Company A | 12/31/2017 | AL | State TI | 50000 |
Company A | 12/31/2017 | AL | Appt % | 0.5 |
Company A | 12/31/2017 | AL | State Appt. TI | 25000 |
Company A | 12/31/2017 | AL | Tax Rate | 0.065 |
Company A | 12/31/2017 | AL | Income Tax | 1625 |
Company B | 12/31/2017 | AL | Federal TI | 50000 |
Company B | 12/31/2017 | AL | State Mods | -50000 |
Company B | 12/31/2017 | AL | State TI | 0 |
Company B | 12/31/2017 | AL | Appt % | 0.5 |
Company B | 12/31/2017 | AL | State Appt. TI | 0 |
Company B | 12/31/2017 | AL | Tax Rate | 0.065 |
Company B | 12/31/2017 | AL | Income Tax | 0 |
Desired Matrix:
Entity | Company A | Company B |
Fiscal Year | 12/31/2017 | 12/31/2017 |
Name | AL | AL |
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 Rate | 6.50% | 6.50% |
Income Tax | 1,625 | - |
Solved! Go to Solution.
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
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
@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!!
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |