Transposing data for line chart?

[Anonymous]

Hi All,

I want to make a line chart from the following data:

I want Dates on the X Axis and then number values (for each colour) on the Y Axis.  Then I want the line values to be Ebitda YTD Actual, EBITDA YTD Budget & EBITDA PY.

I have tried transposing the data to make it suitable for the line graph but to no avail.  Can anyone asssist?

Thanks!

[v-stephen-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[Anonymous]

It is working well, but now I am trying to make a line chart.  I want a line for each metric, but when I try to do this, it adds the vaue for each metric to the same line, see below:

e.g I want one line for Revenue YTD Budget and a line for Revenue YTD PY - not just one line that adds them all together.

[v-stephen-msft]

Hi @Anonymous ,

It is suggested to unpivot the color columns.

Select the color columns and then click 'Unpivot Columns'.

Then create a line chart as follows.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for this.
Do you know the best way I could work with this data.  Or anywhere I can read more to understand this. I'm finding it difficult to work with data with 3 main parameters 'Metric' 'Date' & 'Color'.  For example I may want to look at some (not all) of the metrics by date and filter by color.  It seems as if I have to sacrifice one and work with 2 of the 3.  

[v-stephen-msft]

Hi @Anonymous ,

Matrix with drilling down/up is good. About filtering, you can filter by the filters or the slicers.

Reference: Drill down and drill up in a visual - Power BI | Microsoft Docs

Add a filter to a report in Power BI - Power BI | Microsoft Docs

Slicers in Power BI - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@Anonymous can you please share the data in the text format which can be copied.

[Anonymous]

Metric	Date	Blue	Red	Green	Orange
Rev YTD Actual	28-Feb-22	100.00	1793.00	100.00	1822.00
Rev YTD Budget	28-Feb-22	1279.00	1670.00	1279.00	1670.00
Rev YTD PY	28-Feb-22	1237.00	15.00	1237.00	5400.00
EBITDA YTD Actual	28-Feb-22	1256.00	50746.00	1256.00	55495.00
EBITDA YTD Budget	28-Feb-22	1267.00	101091.00	1267.00	97685.00
EBITDA YTD PY	28-Feb-22	1657.00	2025.00	1657.00	30204.00
Costs YTD vs Budget	28-Feb-22	1987.00	1111.00	1987.00	-4.00
Bonus Retention Collection % (7 Mar)	28-Feb-22	1778.00	1700.00	1778.00	0.22
Rev YTD Actual	31-Mar-22	2513.00	1800.00	2513.00	2360.00
Rev YTD Budget	31-Mar-22	1831.00	1442.00	1831.00	3160.00
Rev YTD PY	31-Mar-22	2000.00	1235.00	2000.00	2180.00