Quarterly Trend in Power bi matrix

Problem statement – 

Business wants to see Quarterly/monthly trend of KPI/Measure in Power bi.

However it is known that in power bi Matrix/visual the Quarterly trend of multiple KPIs cannot be viewed.

By default the Power bi Matrix takes rows ,columns and values – in this combination the values are always displayed below the columns .

Available view

Desired View

Solution-

PBIX File -- Solution_PBIX 

There are 2 scenarios in this case 

Scenario 1 --  No calculated columns on the KPIs

To get that view follow the steps :-

1. Transform data --> select the KPIs columns
2. Goto transform data tab --> selected unpivot only selected columns
3. Arrange the values in the matrix as shown and now you can have the view of Actuals by Year/Quarter/Month

Scenario2 -

Next Scenario is where ,you need to do some calculation on the KPI . in this case the above simple unpivot will not work ,

As there the calculation will be performed on DAX and for that new calculated columns will be required , these columns can not be pivoted.

Follow these steps to achieve the functionality -

1. Add Custom columns with value =1 , add the same number of columns as the number of kpis you want to use , in my case i have 2 Kpis actuals and outlook , thus i have used 2 custom columns. Dummy and dummy1
2. Unpivot the custom columns
3. Create conditional columns , such that dummy will have value for actuals and dummy1 for outlook
4. now you can perform the calculations on Dummy and dummy1 considering them as the KPIs
5. here Actual Sales Dummy__ and Outlook Sales Dummy1__ are created and added into a new Measure
6. Also a new column is created for attribute and for giving the name matching to the KPIS
7. Now use rename attribute column and value for attribute column in your matrix to get the final view

Feel free to use any optimization approach in the solution