Tool Tips

ColinHaze

Hi,

I am trying to use tooltips for the first time so that I can add it to the data label on my bar chart. I know when you use a pivot table you have options to do % of subtotal for certain columns. Is there a way to do that in the tool tip field?

jessdoe

You'll need to first create a beastmode calculating the percentage and use that as the tooltip field. Make sure you configure the format on the tooltip column as a percentage.

ColinHaze

jessdoe I am having a hard time creating this beastmode.

The beastmode I am using is SUM(Loads) OVER (PARTITION BY CARRIER_NAME) / SUM(Loads)

What could I be doing Wrong?

MichelleH

@ColinHaze You can also try using the %_PERCENT_OF_CATEGORY macro in the Hover Text. There is also a an option for percent of total.

jessdoe

Is carrier_name your X axis and loads your Y? If so your data is already partitioned by carrier_name. What are you trying to subtotal? Maybe paste a screenshot of your bar graph if you can.

ColinHaze

Here is the graph. I am trying to get 32.71% (which is in the piviot table) to show in the first tier when I hover over Kinard. Concentration is just the % of subtotal in the pivot table.

jessdoe

Try

sum(sum(cpm_beastmode)) over(partition by carrier_name, month(`fiscal months`)) /

sum(sum(cpm_beastmode)) over(partition by carrier_name, year(`fiscal months`))

ColinHaze

That didnt seem to work. Looking at Kinard for 7-1-2023 that beastmode gave me 17%

jessdoe

What's your cpm formula?

ColinHaze

SUM(Carrier Pay) / SUM(Total billed miles)

jessdoe

Hmm I don't know, you can try adding the distinct command as in sum(sum (distinct cpm formula)) but I you may have to aggregate that in the ETL first. Or use Michelle's hover text option if that rolls up correctly.

ColinHaze

Thank you for trying! Ill keep playing around with it!

jessdoe

Yeah let me know if you do figure it out please Colin

ST_Superman

@ColinHaze

Could you walk me through the math for Kinard Trucking for July that you are looking for?

Do you want to see:

(sum of Kinard for July) / (sum of Kinard for entire time period)

(6.64) / (38.13) = ~17%

or

(sum of Kinard for July) / (sum of all carriers in July)

(6.64) / (41.35) = ~16%

Neither of these seem to get to 37%

ColinHaze

ST_-Superman-_

The value I want the chart to display is the CPM(which it is doing correctly) The problem comes in with the hover text.

Here is my logic using a simple example.

For the month of 7/1/2023

Kinard has 34 loads

The other companies like kkw, power logistics, etc… have different # of loads. Lets just say all the loads summed together = 100 so Kinard has 34/100 = 34% concentration. Ideally I want to do this for each month and allow the user to hover over the bar chart and get the % concentration for the month. Does that make sense?