Bar chart grouping by one field but labeling with another

[gk2go]

Suppose I have this table:

I want to create a bar chart like this one (view by url), but instead of displaying the URL I want to display the page name as the axis labels (so as if view by title but with the values of view by url).

The problem is that there are multiple titles per URL, so the issue is which one to choose. It could be the first. How to accomplish this neatly in Power BI?

I've uploaded groupby-url-display-title.pbix 

[Ashish_Mathur]

Hi,

Please show your exact expected result.  Just show your expected result in a Table.  Thereafter we can always switch the Table to a column/bar visual.

[gk2go]

@Anonymous, your measure selects the right labels/titles, but gives 4 views for the abc.html, it should be 8.

@Ashish_Mathur 

Title                              | Path                                                |  Views

ABC Super Super Title  | www.example.com/abc.html          | 8

DEF Title                       | www.example.com/def                   | 2 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[gk2go]

This doesn't work. You've only stripped parts of the URL and not used the dynamic_title at all.

Instead of abc and def as rows, you should have ABC Super Super Title and DEF Title as shown in my table above.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[gk2go]

Thank you @Ashish_Mathur .

This worked but on a large table it takes forever to run. I've kept it running a server for over 24 hours and then aborted it as it showed it was still at ~10% of the rows. Isn't there a more efficient solution?

Your solution:

    Partition = Table.Group(#"Changed Type", {"URL"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Title", "Views", "Index"}, {"Title", "Views", "Index"}),
    #"Grouped Rows" = Table.Group(#"Expanded Partition", {"URL"}, {{"Max index for that URL", each List.Max([Index]), type number}}),
    Joined = Table.Join(#"Expanded Partition", "URL", #"Grouped Rows", "URL"),
    #"Added Custom" = Table.AddColumn(Joined, "Title to be considered", each if [Index] = [Max index for that URL] then [Title] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"URL", Order.Ascending}, {"Index", Order.Descending}, {"Max index for that URL", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Title to be considered"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Max index for that URL"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Views", type number}})

[Ashish_Mathur]

Sorry, i would not know.

[v-diye-msft]

Hi 

Not sure if you’d like to show the chart as below, similar with view by url but replace the url with the first title:

If so, you can use group. Right click the title and group, change the display name using the first title.

Pbix attached: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EZ9YBANxgdpNtg3yU9CGR2...

Best regards,

Dina Ye

[Anonymous]

Hi @gk2go  , 

Create a new column:

Column = CALCULATE( MIN( Table1[Title] ) , ALLEXCEPT( Table1 , Table1[URL] ))

Let me know if that helps,
Cheers
Robin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gk2go]

Thank you @Anonymous . That worked until i introduce a row with an empty title:

Then blank wins:

How to make sure it's not blank that shows up? Can the non-blank title with the highest views be the one selected? 

[Anonymous]

Hi @gk2go  ,

Try adding Table1[Title] <> BLANK() as below:

Column = CALCULATE( MIN( Table1[Title] ) , ALLEXCEPT( Table1 , Table1[URL] ), Table1[Title] <> BLANK() )

Let me know if that works.
Cheers,
Robin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gk2go]

@Anonymouscan you make this pick the non-blank title with the highest views?

[Anonymous]

Hi @gk2go  , 

No worries , here it is:

Measure = IF( CALCULATE( MAX( Table1[VIEWS] ) , ALLEXCEPT( Table1 , Table1[URL] ) , Table1[TITLE] <> BLANK() ) = MAX( Table1[VIEWS]) , MAX( Table1[TITLE]) , BLANK() )


Cheers,

Robin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gk2go]

It's not working as expected, see:

[Anonymous]

Hi @gk2go ,

I would suggest to use the Measure as a filter only:

Measure = IF( CALCULATE( MAX( Table1[VIEWS] ) , ALLEXCEPT( Table1 , Table1[URL] ) ,Table1[TITLE] <> BLANK() ) = MAX( Table1[VIEWS]) , 1 , BLANK() )



Please try the same as screenshot and let me know if that suits.

Cheers, 
Rob

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.