Calculate by month (totaling in 100% stacked column chart)

[aellison]

I am trying to display the Month totals in the ToolTip for the below chart. This is a 100% Stacked Column Chart with [Fiscal Month] on the Axis, [Lag Time Group] on the Legend and [Count of Appointments] as the value. What I would like to show is the Total for each month, along with the default of Total by Month for each lag time group on the ToolTip, but I'm not sure what measure to create to do that. Example: The Total Lag Time in the ToolTip for September below should be 2335

Any thoughts?

Thank you in advance,

[Sean · Accepted solution]

@aellison I've been trying but can't duplicate your results.

For me both ALL ( Table[Legend Column] ) and ALLEXCEPT ( Table, Table[Legend Column] ) both work.

The only thing I could think of is to try COUNTROWS which also works!

COUNT only counts numbers and dates COUNTROWS picks up everything!

Total Lag Time = CALCULATE ( COUNTROWS ( 'Table1' ), ALL ( 'Table1'[Lag Time Group] ) )

[mmanwaring]

Hi

You need a calculate for count of appointment lag times with all days.

something like this CALCULATE([Count of Appointments], All(Table[lag time group])) for example

Regards

Mike

[aellison]

I'm still getting the same results with the following expression:

Total Lag Time =
               CALCULATE(
                         COUNT('Table1'[Appointment Lag Time])
                         , ALL('Table1'[Lag Time Group])
                        )

[Sean]

@aellison This should work...

Total Lag Time =
CALCULATE (
    COUNT ( 'Table1'[Appointment Lag Time] ),
    ALLEXCEPT ( 'Table1', 'Table1'[Fiscal Month] )
)

[aellison]

When doing that one, it's a strange result. Doing the ALLEXCEPT for Fiscal Month gives me a total for all the green sections when hovering over any one of the green sections, If I change it to ALLEXCEPT for Lag Time Groups, I get the exact same result (total for all the green sections). I just need it to pivot the other axis ...

[Sean · Accepted solution]

@aellison I've been trying but can't duplicate your results.

For me both ALL ( Table[Legend Column] ) and ALLEXCEPT ( Table, Table[Legend Column] ) both work.

The only thing I could think of is to try COUNTROWS which also works!

COUNT only counts numbers and dates COUNTROWS picks up everything!

Total Lag Time = CALCULATE ( COUNTROWS ( 'Table1' ), ALL ( 'Table1'[Lag Time Group] ) )

[aellison · Accepted solution]

I ended up creating a custom column for each of the Lag Time Groups and putting each of those columns in to the Value field. This gave me the ToolTip results that I needed. Thank you for your time.