Display two columns on same axis

[sharc316]

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.

[v-jiascu-msft · Accepted solution]

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.

[v-jiascu-msft · Accepted solution]

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.

[sharc316]

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?

[Anonymous · Accepted solution]

Unpivoting the data does change how the data is stored.  This means you'll need to do one of two things:

1. Just check how your other visuals are set up (including any measures) and update for the new format
2. 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.

[sharc316]

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?

[Anonymous]

That will only be the case due to how you've related the tables.  Give it the same relations as you original table.

[sharc316]

It wont let me create a relationship as there is no column with unique values.

[v-jiascu-msft]

@sharc316

Hi, I am so glad to tell you I really found an easy way. You can have a try.

1. We need a table with all the drivers. (here we have a table Drivers)

2. Create two relationships between Drivers and Table1 (here we have), only one is active.

3. Create a calculated column in Table1.

IfNull =
IF ( ISBLANK ( Table1[Driver #2] ), 0, 1 )

4. Create a measure.

TotalMiles1 =
SUMX ( Table1, IF ( Table1[IfNull] = 0, Table1[Miles], Table1[Miles] / 2 ) )
+ CALCULATE (
SUMX ( Table1, IF ( Table1[IfNull] = 1, Table1[Miles] / 2, 0 ) ),
USERELATIONSHIP ( Table1[Driver #2], Drivers[Driver] )
)

There will be a little by-product (a blank driver), but it won't do any harm.

[sharc316]

@v-jiascu-msftThank you for taking the time to provide such detailed instructions. I'm afraid that my situation is a bit more complicated. I have an appended table from where most of my visuals are pulled from. I've duplicated that able so that I can unpivot the Driver #1 and Driver #2 columns. When I did that in the original appended table it messed up almost all other graphs.

When the tables were appended there were a lot of fields that were not part of other tables which would be blank for a lot of the rows and because of this reason no relationships can be established.

I think the only way to get a primary key here would be to create a separate column and just populate it with row counts for as many rows as there is data and for any new data that comes out every week. Would my approach be somewhat accurate here?

[v-jiascu-msft]

@sharc316

Hi,

Maybe something isn't clear here. In the lastest method, we don't need to unpivot any columns. The relationships are created between drivers. Blank values won't have an effect. You should a table of all the unique drivers. The relationships would be Driver->Driver #1 and Driver ->Driver #2. The cardinality is 1:many. We can have blank values in Many-side.

This method won't change the structure of table. So the other visuls won't be messed up. You could have a try.

[sharc316]

hmm ok I see what you're saying. I'm goint to need a way to pull the driver #'s from the two columns into one for the new table automatically so I dont have to add everytime there is a new driver. I'll give it a try. Thanks very much for your help.

[v-jiascu-msft]

My pleasure. That could be an annoyance. Maybe it's easy to do it in an Excel. Firstly, creating a table of drivers in Excel, then import to Power BI. Everytime new drivers added, you can refresh from Power BI. Adding driver in excel would be easier. Append Driver #2 to Driver #1, remove duplicates. Hope this could be a little help.

[Anonymous]

This will really fall back onto the overall structure of your tables and project.  There will be a way to relate them all together.

[sharc316]

Duplicated the table and it works pretty well. Thank you both.

[sharc316]

I think creating a new table is a safer way to go. I'll try it this way. Thank you.

[Anonymous]

I'm thinking you need to unpivot the data first.  If the data is like this in your data sort, Power Query (in the Edit Queries section) can do this for you pretty easily.