Forum Discussion

Mladen's avatar
Mladen
Regular Visitor
9 years ago
Solved

Power BI Basics - Column charts

Hallo Everybody,   I just started using Power BI to produce some basic reports for our Management, but I'm not really able to produce the desired Visualizations. It should be something very simple ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Mladen,

    The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?

    Not like measures, calculate tables/columns don't respond to user selections on the report. So your new created table won't be affected by the slicers on the your report.

     

    After reviewing your original dataset, I would suggest you use Unpivot option under Query Editor > Transform to unpivot your "2012 - product AAA - Sales", ... , "2016 - product BBB - Sales" columns first.

     

     

    Then, Split the "Attribute" column with "-".

     

     

    And Rename the columns like below.

     

     

    Last, you should be able to use a Matrix visual with "Product" as Rows, "Year" as Columns, and "Sales Revenue" as Values on the report. :smileyhappy:

     

     

    Regards

Mladen's avatar
Mladen
Regular Visitor
9 years ago

Yeah, that's exacly what I would like to produce. Can someone tell me what a DAX for generating such a new table should be? Should I then estabish relationships between the tables manually?

Mladen's avatar
Mladen
Regular Visitor
9 years ago
Allright, I found a workaround to create the new table with:
 
NewTable =
UNION (
 ROW ( "Year"; 2012; "Sales Prduct A"; SUM(Table1[ProductAAA]; "Sales Prduct B"; SUM(table1[ProductBBB]))
 ROW ( "Year"; 2013; "Sales Prduct A"; SUM(Table1[ProductAAA]; "Sales Prduct B"; SUM(table1[ProductBBB]))
......
)
 
The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?
 
Thanks very much for your help guys! best regards,
 
Mladen
  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Mladen,

    The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?

    Not like measures, calculate tables/columns don't respond to user selections on the report. So your new created table won't be affected by the slicers on the your report.

     

    After reviewing your original dataset, I would suggest you use Unpivot option under Query Editor > Transform to unpivot your "2012 - product AAA - Sales", ... , "2016 - product BBB - Sales" columns first.

     

     

    Then, Split the "Attribute" column with "-".

     

     

    And Rename the columns like below.

     

     

    Last, you should be able to use a Matrix visual with "Product" as Rows, "Year" as Columns, and "Sales Revenue" as Values on the report. :smileyhappy:

     

     

    Regards

    • Mladen's avatar
      Mladen
      Regular Visitor
      9 years ago

      Hello,

       

      Thanks very much for the tip. I think it goes in the right direction. Nevertheless I have more than 1000 rows (employees) and 30 columns (5 years x 6 products). When I unpivot, it increases the number of rows from 1000 to 30'000 (30*1000), it breakes other graphs I produced with my original dataset and I get errors in some rows (probably related to some errors in my original dataset). I'll try to produce the desired graph by cleaning my dataset first, then duplicate the query to preserve the original graphs and than try with the unpivot method. Hopfully it works!

       

      Thank you very much for the support! It was very much appreciated!