Loading data in Combo Charts

[MbProg]

I have a data table with the following structure and data:

Fields: Customer - Year- Income of year - IncomeJanaury - IncomeFebruary- IncomeMarch-....- IncomeDecember

Data:

Some Company X - 2012 - 234 - 12 -23 - 122 - ....- 34

Some Company X - 2013 - 448 - 23 - 12 - 492- ...- 93

Some Company Y - 2012 - 234 - 12 - 23 - 312- .... - 32

Now I want to show this data with Combo Charts so that I can draw a chart for a customer with Y-Axis for the incomes and on the X-Axis for the years. Is that possible in Power BI due to the face that the table has the mentioned structure? Is there a way?

[ankitpatira]

@MbProg all you have to do is go to query editor, select all income columns and then click Unpivot columns as shown below. Then use years column for x-axis and unpivoted Values column for y-axis.

[MbProg]

Thanks @ankitpatira, this helped me a lot. Now I am able to show the incomes of the months as columns in the Combo Charts. Furthermore I am looking for the following function, but I don't know if it is possible: I want to show the income of every month as a column as I am doing right now, but I also want to show next to each income column a specific month of the year another column which shows the income the same month of the previous year. That means something like this pic:

Do you a way to realize that?

[Eric_Zhang · Accepted solution]

@MbProg

You can use SAMEPERIODLASTYEAR in a measure as

sameMonLastYr = CALCULATE(SUM('Table'[sales]),SAMEPERIODLASTYEAR('Table'[DATE])) 

[MbProg]

Hi @Eric_Zhang,

I tried the sample you gave as a measure and as a new column, but it does not give any value. I have even created a simple data tabel in Excel and imported it in a new Power BI, but I still don't get any values. The sample data looks like this:

Date  Value

0	01.01.2015
0	01.02.2015
0	01.03.2015
79	01.04.2015
0	01.05.2015
0	01.06.2015
500	01.07.2015
0	01.08.2015
0	01.09.2015
0	01.10.2015
0	01.11.2015
0	01.12.2015
64305	01.01.2014
0	01.02.2014
0	01.03.2014
2222	01.04.2014
23500	01.05.2014
392,8	01.06.2014
0	01.07.2014
0	01.08.2014
0	01.09.2014
2000	01.10.2014
28664	01.11.2014
0	01.12.2014

But when I enter the following code, I get no data:

New Column: WertMonLastYr = CALCULATE(SUM('Tabelle1'[Column1]);SAMEPERIODLASTYEAR('Tabelle1'[Column2]))

Measure: sameMonLastYr = CALCULATE(SUM('Tabelle1'[Column1]);SAMEPERIODLASTYEAR('Tabelle1'[Column2])) 

I tried to enter the sameMonLastYr in the "Column Series" in the Combo charts, but I still get no value for it (it stays the same).

What am I doing wrong?

[Eric_Zhang]

@MbProg

Enter the sameMonLastYr in the "column values"in the Combo charts, not in the "Column Series" and it should work.

Check the attached pbix.

[MbProg]

@Eric_Zhang I see that you are using Column Hierarchie for the date column. That was exactly the difference between mine and yours and why I was getting an error. Can you tell me what the Hierarchie is doing? And I noticed that the drilldown button disappears when you use hierarchie.

[Eric_Zhang]

@MbProg

Is that working in your case?

The hierarchy stops drilling down, if you don't want drill down your data from year->quarter->month->day.

[MbProg]

@Eric_Zhang Is it possible to drill down the hierarchie of date? I don't find any possiblity to do this. And can you tell me what the hierachie is doing and when to use it?

[Eric_Zhang]

@MbProg

It depends on your requirement. Check Visual hierarchies and drill-down behavior.