Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Deepansh_
Regular Visitor

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

Deepansh__0-1701590912770.png

 

Desired View

Deepansh__1-1701590912781.png

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

 

1 ACCEPTED SOLUTION
Deepansh_
Regular Visitor

Thanks for the response , i belive that i have already shared the solution in my post and working file along with that. Please go through and let me know if any further explanation is needed.
Happy to help 🙂 

View solution in original post

2 REPLIES 2
Deepansh_
Regular Visitor

Thanks for the response , i belive that i have already shared the solution in my post and working file along with that. Please go through and let me know if any further explanation is needed.
Happy to help 🙂 

lbendlin
Super User
Super User

You need to use an additional disconnected table with the desired buckets ("Actual Sales", "Outlook")  and then use that field for the column hierarchy.

 

Let me know if this is enough guidance or if you want me to show you based on your sample file.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.