Forum Discussion

onedayover's avatar
onedayover
Icon for Helper III rankHelper III
2 years ago
Solved

Help with a line and clustered column chart

Hi, I'm hoping someone can help me with how to do this. I have a table that looks like this...

dateregionattributevalue
1/07/2023GARtarget_202378
1/07/2023YXRtarget_202329
1/07/2023BMRtarget_202378
1/07/2023GARactual_202382
1/07/2023YXRactual_202331
1/07/2023BMRactual_202382
1/07/2024GARtarget_2024166
1/07/2024YXRtarget_202454
1/07/2024BMRtarget_2024180
1/07/2024GARactual_202482
1/07/2024YXRactual_202431
1/07/2024BMRactual_202482
1/07/2025GARtarget_2025248
1/07/2025YXRtarget_202581
1/07/2025BMRtarget_2025271
1/07/2026GARtarget_2026290
1/07/2026YXRtarget_202695
1/07/2026BMRtarget_2026315
1/07/2027GARtarget_2027310
1/07/2027YXRtarget_2027101
1/07/2027BMRtarget_2027338


The outcome I'm hoping for is a line and clustered column chart, where the line values are the summed if the attribute column contains "target" (Line y-axis) and the bars are summed if it contains "actual" (Column y-axis).

I will use a slicer that will filter this finished chart by region.
Thanks 🙂

DAX
Help
Line and clustered column chart
  • Anonymous's avatar
    Anonymous
    2 years ago

    This is the source data

    Split column by "_" delimiter

    Renaming the columns

     

    Created mesures for Actual and Target

     

     

     

    Final output with target in line and actual in column and x axis (date) formatted in Categorical.

7 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    Hello onedayover 

     

    please check if this can accomodate your need.

     

    Target = CALCULATE(SUM('Table'[value]),CONTAINSSTRING('Table'[attribute],"target"))
    Actual = CALCULATE(SUM('Table'[value]),CONTAINSSTRING('Table'[attribute],"actual"))
     
    Seems your value column doesnt have any sum value (those categories only have one value, ie: actual_2024 only have one value).
     
    Please check this DAX to your real data.

    Hope this will help you.
    Thank you.
  • onedayover's avatar
    onedayover
    Icon for Helper III rankHelper III
    2 years ago

    Hi Irwan, thanks for getting back to me. I applied your DAX to my real data and I get the line for the "target" values but no bar for the "actual" values. I have set the x and y axis as per your screenshot. Any ideas what might be wrong? And sorry, the data I provided for "actual_2024" attributes were a copy and paste and I forgot to change these figures to something different.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    This is the source data

    Split column by "_" delimiter

    Renaming the columns

     

    Created mesures for Actual and Target

     

     

     

    Final output with target in line and actual in column and x axis (date) formatted in Categorical.

    • onedayover's avatar
      onedayover
      Icon for Helper III rankHelper III
      2 years ago

      Hi Rakesh1705, thanks, I followed your screenshots and created the measures but I still can't seem to get the "actual" values (bars) on my chart, including formatting as Categorical. Any ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Did you select line and clustered column chart from the visual

    Also create the mesures as shown the prior snapshots.

    • onedayover's avatar
      onedayover
      Icon for Helper III rankHelper III
      2 years ago

      Oh hang on, I forgot to change one thing in my original data. It's working now ... thanks so much!