Dynamic Line and Column Chart

[JoanG]

Hi There,

I want to use a slicer to choose Total Production (EA), or Energy Usage (MJ), or Total Production (KG).

When I choose either one, x-axis is month and y-axis to the value. Please note the month also contained FY and YTD, both are needed to be shown in the same chart, put after Dec, but don't connect the data point with another month (Jan-Dec) for the line chart. Bar chart is for the Target and line chart is for the Actual.

I have created the raw data in the link. The data is not real.

https://docs.google.com/spreadsheets/d/1Vb_3HyEbx_IwaCplZQ2Gs3-KXMBBAkt_/edit?usp=sharing&ouid=10607...

Thanks in advance for the help.

Regards,

Joan

[HotChilli]

I think you could Unpivot the numeric columns in Power Query.

Write a couple of measures : One to sum the values for Target, one to sum the values for Actual.

Use those in the relevant y-axis field wells.

--

The month would go on the x-axis and the sorting would probably be the most challenging task.  I would probably create a dimension table for month (including YTD and FY) with an additional sort order column, then use 'Sort by Column' feature. Create a relationship to Fact table.

[Deepak_]

Hi @JoanG ,

Can you please share what do you want to see in the report ?


Thank you.

[JoanG]

hi @Deepak_ ,

Please see the illustration from below link:

https://docs.google.com/presentation/d/12eYN5lOc7L6_N_XSyGp-4L6uk_1QYCeJ/edit?usp=sharing&ouid=10607...

Thanks!

Regards,

Joan