topic Re: Help with a line and clustered column chart in DAX Commands and Tips

Help with a line and clustered column chart

onedayover — Mon, 01 Jul 2024 03:52:25 GMT

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

date	region	attribute	value
1/07/2023	GAR	target_2023	78
1/07/2023	YXR	target_2023	29
1/07/2023	BMR	target_2023	78
1/07/2023	GAR	actual_2023	82
1/07/2023	YXR	actual_2023	31
1/07/2023	BMR	actual_2023	82
1/07/2024	GAR	target_2024	166
1/07/2024	YXR	target_2024	54
1/07/2024	BMR	target_2024	180
1/07/2024	GAR	actual_2024	82
1/07/2024	YXR	actual_2024	31
1/07/2024	BMR	actual_2024	82
1/07/2025	GAR	target_2025	248
1/07/2025	YXR	target_2025	81
1/07/2025	BMR	target_2025	271
1/07/2026	GAR	target_2026	290
1/07/2026	YXR	target_2026	95
1/07/2026	BMR	target_2026	315
1/07/2027	GAR	target_2027	310
1/07/2027	YXR	target_2027	101
1/07/2027	BMR	target_2027	338

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 🙂

Re: Help with a line and clustered column chart

Irwan — Mon, 01 Jul 2024 04:22:57 GMT

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.

Re: Help with a line and clustered column chart

onedayover — Mon, 01 Jul 2024 04:38:31 GMT

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.

Re: Help with a line and clustered column chart

Anonymous — Mon, 01 Jul 2024 04:52:17 GMT

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.

Re: Help with a line and clustered column chart

onedayover — Mon, 01 Jul 2024 05:20:13 GMT

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?

Re: Help with a line and clustered column chart

Anonymous — Mon, 01 Jul 2024 05:25:27 GMT

Did you select line and clustered column chart from the visual

Also create the mesures as shown the prior snapshots.

Re: Help with a line and clustered column chart

onedayover — Mon, 01 Jul 2024 05:28:21 GMT

Yes I did, that exact visual

Re: Help with a line and clustered column chart

onedayover — Mon, 01 Jul 2024 05:30:56 GMT

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