Forum Discussion
Display two columns on same axis
Hi,
Wondering if there is a way to display data from columns on one axis. See screenshot below; i have driver 1 and driver 2 columns and i would like to display miles driven by each driver. Each row lists a trip and then has a number of miles for that trip. If the trip is short then it will have only Driver #1 and Driver #2 will be blank. If it's a longer trip then it will have Driver #1 and Driver #2 populated.
I've created a new column that will calculaate the miles driven for each driver. If column #2 is blank then full mileage is displayed and if Driver #2 has a driver #2 is populated then it will divide the miles by 2 to split between the two drivers. I would like to now display this in a chart showing the sum from this new column by each driver from columns Driver #1 and Driver #2. I was only able to get one column to show.
Please suggest any solutions.
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.
15 Replies
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.
