Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

                           

 

 



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 ())

3 Replies