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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Trying to replicate an Excel table in Power Bi

Hello Community  -  The challenge is a I need to replicate this table from Excel.   The issue I am facing is that the data is pivoted differently in my ERP tables (in Power Bi).    And each of the values below is coming from different tables in ERP  (Orders, Shipments, and Opportunities).    I have measures for each of these sums, but the challenge is that I need to be able show the "name" of the measure, and it's corresponding value, on the same row  (in a matrix table).  

 

The ERP tables are not structured this way so trying to put the values on the Rows in a matrix of course is not working. 

 

I tried an idea I saw whereby I created an "unpivot table" with an index that corresponded to each of the measures I would need to use to replicate the Excel table.   Then create a calculated column and use that to add the values based on the index.   It's not working.   

 

Looking for another approach?    And just to reiterate what the output should look like in a matrix table: 

                                               

                                                        Months (on column, from date table)

Measure Name (on rows)

                                                             Jan         Feb        Mar

                           Net Revenue             $503      $205      $309    (measure values would correspond to the measure name)

                           Booked Orders         $345      $398      $234

                           Opportunity Value    $34        $76        $86

                           

 

 

texmexdragon_0-1644090028721.png



New Forecast Template =

SWITCH (

SELECTEDVALUE ( 'Unpivot Table'[Index] ),
1, VALUE ( [Sum of Net Revenue] ),
2, VALUE( [Shipped Revenue] ),
3, VALUE( [Order Value] ),
4, VALUE ( 'Sf_Opportunity Line'[Sum of Opportunity Value] ),
 
BLANK ())
1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

Have you tried using "Show on Rows" in the matrix?

Think it might help.

https://datasavvy.me/2017/08/10/you-can-now-put-values-on-rows-in-power-bi/amp/



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

3 REPLIES 3
bcdobbs
Super User
Super User

Have you tried using "Show on Rows" in the matrix?

Think it might help.

https://datasavvy.me/2017/08/10/you-can-now-put-values-on-rows-in-power-bi/amp/



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Anonymous
Not applicable

@bcdobbs   How on earth did I miss that??    Exactly what I needed....thank you!

It's ok, I had the same feeling when I first came across it!



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors