Forum Discussion
Display two columns on same axis
Hi, sharc316,
Anonymous has given a good advice. Follow the steps below, you would get what you want.
1. Open Query Editor, add a conditional column as the image 1 showed. Change the type of column "IfNull" into Whole Number.
2. Choose columns "Driver #1" and "Driver #2" at the same time, and then click "Unpivot Column". You will see the result. Rename the last column to "Driver".
3. Create a measure.
TotalMiles =
SUMX (
Table1,
IF ( 'Table1'[IfNull] = 1, 'Table1'[Miles] / 2, 'Table1'[Miles] )
)It's done now. You can have a try.
Unpivoting the data does change how the data is stored. This means you'll need to do one of two things:
- Just check how your other visuals are set up (including any measures) and update for the new format
- Instead of unpivoting the original table, make a copy of it in the 'Edit Queries' section, and make it a new table. This will allow you to have visuals using the old way if you have no other option.
Hi, sharc316,
Anonymous has given a good advice. Follow the steps below, you would get what you want.
1. Open Query Editor, add a conditional column as the image 1 showed. Change the type of column "IfNull" into Whole Number.
2. Choose columns "Driver #1" and "Driver #2" at the same time, and then click "Unpivot Column". You will see the result. Rename the last column to "Driver".
3. Create a measure.
TotalMiles =
SUMX (
Table1,
IF ( 'Table1'[IfNull] = 1, 'Table1'[Miles] / 2, 'Table1'[Miles] )
)
It's done now. You can have a try.
Thank you for posting a solution. When I unpivot the driver columns it seems that it messes up the data. The other visuals either have errors or do not display correctly. Is this supposed to happen?
Unpivoting the data does change how the data is stored. This means you'll need to do one of two things:
- Just check how your other visuals are set up (including any measures) and update for the new format
- Instead of unpivoting the original table, make a copy of it in the 'Edit Queries' section, and make it a new table. This will allow you to have visuals using the old way if you have no other option.
I think creating a new table is a safer way to go. I'll try it this way. Thank you.
Duplicated the table and it works pretty well. Thank you both.
Just realized that the date filter I have on my report will not work on this tile because it's from a different table. Any other solution to this? Maybe some way to use the original table without unpivoting?
That will only be the case due to how you've related the tables. Give it the same relations as you original table.