Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

plotting chart with values in multiple column (as pivot chart)

Hi all,

Very new to power BI and eager to learn and be involved in the community =)

First i will show what i did previously using powerquerry and pivot chart in excel 2016 and then i show where i am stuck in powerBI

Basically i have a simple table querry from SQL:

https://drive.google.com/open?id=1Qr-AdHMmBCBx4ZsuSw_YEKOWzz7hU6hM

Then after , using a pivottable i put the year and values in columns in pivottable field and the pivot chart shows what i want:

https://drive.google.com/open?id=1kAk_p8agZKAeI5DUbbWbIid3usFJGZ-P

*ignore the 2nd Y axis (bar chart) for the moment.

 

Using Get data in powerBI i querry the same table and i am able to get:

https://drive.google.com/open?id=1d9Fs_JrBUXw9kWpFvjb3-59KK0JR4-7F

 

I am able to get the a chart of the Average Price, Min Price, Max Price  vs Day or Average price of 2017, 2018, 2019 vs Day but i like to have all 9 lines in the same chart ( as per excel)

I have been trying this for the past few hours but i cant seem to figure it out weather its by column hierachy etc. I have transform the Date into seperate 3 columns to be year, Month, Date so i could avoid the Date Hierachy but it doesnt work as well.

 

Just to make it simple, with the table i have of 4 columns (Date, PriceMin, PriceAvg, PriceMax), i want to plot Price vs Month/Date. I want to have 9 series : 2017Min,2017Average,2017max, 2018Min,2018Average,2018max, 2019Min,2019Average,2019max (same as my excel pivot chart)

 

PS: Fecha is Date and Precio is Price. I do not need the Moving average as seen in my excel example for now but it would be good to have too.

 

Regards

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Just to make things clearer:
    Bascially with my source table below (4 columns sourcedata of Date, Price Min, Price Average, Price Max) , 
    i like to plot Price vs Month/Day with 9 series of 2017Min, 2017Avg, 2017Max, 2018Min, 2018Avg, 2018Max, 2019Min, 2019Avg, 2019Max.

    Prefereably as i have in pivot chart too , a filter for Month.
    Thanks Lots