Forum Discussion

ldenney's avatar
ldenney
Regular Visitor
6 years ago
Solved

Line & Clustered Column Chart

Hello Community -

I am working on a line and clustered column chart. The x axis is fiscal year end and the Y axis are an indicator. The columns are an indicator for a hospital and I need to add 4 benchmark lines with differing values by year. I'm using a slicer to filter the hospital. I'm having trouble organizing the data to accomplish this...I have tried everything in 1 file and I'm currently trying to use the hospital file with 4 joined tables containing data for each benchmark type, indicator and year. Any suggestions on how to accomplish this?

Thank you!

Need Help
  • ldenney's avatar
    ldenney
    6 years ago

    Hi v-lid-msft 

     

    I have tried the "cross filter direction" as both and it doesn't work. I will try the fiscal year column in another calendar table and let you know how it goes.

     

    Regards -

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi ldenney ,

     

    We can try to set the "Cross Filter direction" as both for each benchmark table and hosptial table, or we can use a fiscal year column in another calendar table as the x-axis field.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ldenney's avatar
      ldenney
      Regular Visitor
      6 years ago

      Hi v-lid-msft 

       

      I have tried the "cross filter direction" as both and it doesn't work. I will try the fiscal year column in another calendar table and let you know how it goes.

       

      Regards -

      • ldenney's avatar
        ldenney
        Regular Visitor
        6 years ago

        Hi v-lid-msft - I was able to get the visualization to work last night by using the fiscal year column in another table and linking everything to that table. Thanks so much for your help. I will keep your other suggestion in mind.

         

        Thanks a million!

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi ldenney ,

     

    We can also use the following measures as line values to meet your requirement:

     

    State Acute Indicator 1 =
CALCULATE (
    SUM ( 'State Acute'[State Acute Indicator 1] ),
    FILTER (
        ALLSELECTED ( 'State Acute' ),
        [fy_end_dt] IN FILTERS ( 'Hospital'[Fiscal Year End] )
    )
)

     

    State CAH Indicator 1 =
CALCULATE (
    SUM ( 'State Acute'[State CAH Indicator 1] ),
    FILTER (
        ALLSELECTED ( 'State CAH' ),
        [fy_end_dt] IN FILTERS ( 'Hospital'[Fiscal Year End] )
    )
)

     

    US Actue Indicator 1 =
CALCULATE (
    SUM ( 'US Actue'[US Actue Indicator 1] ),
    FILTER (
        ALLSELECTED ( 'US Actue' ),
        [fy_end_dt] IN FILTERS ( 'Hospital'[Fiscal Year End] )
    )
)

     

    US CAH Indicator 1 =
CALCULATE (
    SUM ( 'State Acute'[US CAH Indicator 1] ),
    FILTER (
        ALLSELECTED ( 'State US' ),
        [fy_end_dt] IN FILTERS ( 'Hospital'[Fiscal Year End] )
    )
)

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.