Comparison reporting on quater and YTD basis.

[powerbisach]

Hi All,

I have a sample data...Which has companies, KPI parameters to compare along with data of all the quaters.

Companies	KPI parameters	Quarter	2015	2016	2017	Diff with previous year	% Diff with previous year
A	KPI1	Q1	9.8	10.50	11.80
A	KPI2	Q1	6.8	4.60	4.75	-0.15
B	KPI1	Q2	3.4	8.70		8.70
B	KPI2	Q2	2.3	5.50		5.50

What i need is to show comparison of companies on KPI parameters selection for Last 6 quaters

And report showing comparison of KPI's for 2 companies for Last 2 years.

I am new to powerbi and need a starting base to solve this problem. Note data is in excel and data is updated when a quater is completed. how to go on with this problem. Any help will be appreciated. Thanks for your support.

[v-yulgu-msft]

Hi @powerbisach,

According to current description, I was not clear about your requirement. Are above columns including 'Diff with previous year' and '% Diff with previous year' all existing fields in source table? Then, what is your desired result? How do you want to compare the values based on quarter? Could you illustrate your requirement with some examples?

Regards,
Yuliana Gu

[powerbisach]

Hi v-yulgu-msft

I have 2 and more companies...having KPI parameters 2 and more....Each KPI is having Values for each quaters...

so basically i want to Compare

Company A = KPI parameter1, KPI parameter2...having values for Q1,Q2,Q3,Q4 with Company B for same parameters and quaters...hope it does make sense...

Its basically comparing company A with company B on some parameters which is having values every quaters...

[v-yulgu-msft]

Hi @powerbisach,

Based on my assumption, perhaps you could try Clustered column chart.

First, if above image represents your source table structure, you should first unpivot it in Query Editor mode. Select columns [Companies], [KPI parameter] and [Quarter] at the same time, then, unpivot other columns like below.

You will get this result.

Return back to visual mode, insert a clustered column chart. 

Not sure whether this meets your requirement. If I have something misunderstood, you could show us your desired output that you can get in Excel.

Regards,
Yuliana Gu

[powerbisach]

Hi v-yulgu-msft

Thanks for your time and reply. Much appreciated

My output should look like comparision of 2 companies KPI as 

It's actually showing Comparision on same page(Tabular form is fine no need of graphs). I have lots of companies...so i need feasibility to change Company B to any other company. Also i would like to show last 3 quaters along with current quater...so i am not sure how to achieve all this. Do i need to use measure..If yes where and how.

[v-yulgu-msft · Accepted solution]

Hi @powerbisach,

First, please unpivot columns to get above table structure mentioned in my above post. Then, insert a Matrix visual, add relative fields, drill down to the Year-Quarter level.

If you want to filter records for a specific company, you could use visual filter in the right panel. Add the column you want to filter to this area, from the drop down list, select any value you want.

Regards,
Yuliana Gu

[powerbisach]

Hi v-yulgu-msft,

Thanks for your help.

Your solution actually display correct data. But the challenge is

1. The User should have a choice to select the companies he want to compare (in terms of visualization), so visual filters do not make sense.

2. Currently it displays all the quarters data, will it be possible to show only last 3 quarters data..

[v-yulgu-msft]

Hi @powerbisach,

---

The User should have a choice to select the companies he want to compare (in terms of visualization), so visual filters do not make sense.

---

Instead of visual filters, you could add a slicer for users to select. You could create two exactly same matrix visual, one is always showing data of company A, another one shows data based on slicer selection. 

---

Currently it displays all the quarters data, will it be possible to show only last 3 quarters data..

---

Here is a similar thread for your reference: show past 4 quarters data

Regards,
Yuliana Gu

[powerbisach]

Hi v-yulgu-msft, (Yuliana )

Thanks for your replies over past few days. Much appreciated. 

1. Instead of Visual filters, I used 2 different slicers and used edit interactions option to get the results. It's a better solution since i can use any companies in both the slicers.

2. For quarter solution i am still struggeling and need to find a solution.

Nevertheless i am truly thankful to you for showing me a right direction to fulfill my needs. Much appreciated.