Oracle table data representation in PowerBI

[moumitadeb]

I have a table in Oracle with data as below: 

Need to represent the data in PowerBI in below format:

I tried transposing the data, but it didn't work as expected. Need Help!

[ToddChitt]

The original issue was resolved. The best way to get help on subsequent requirements is to start a new post related specifically to that issue. 

Hve you looked at Power BI Paginated Reports for this new requirement? 

[moumitadeb]

PowerBI paginated reports I have not tried. Need to explore the same.

[ToddChitt]

It seems the requirements for this solution keep changing.

There is no way that I know if to hide columns in a Matrix visual. If you want [Last Month] minus [The month before] you are going to get it for every month column.

You man need to do this in a Power BI Paginated Report, which gives you much more control over the columns in a Tablix. 

[moumitadeb]

The requirement is same from the beginning , i am trying to achieve one by one. Thanks for your help. Please share if you have any examples for the same.

[ToddChitt]

Are you saying that you need a way to create sub-totals for "On Prem Servers" and "Cloud Servers"? Then you can add manually generated table with Category and Sub-Category as follows:

Category            Sub

======             ===

On Prem             On Prem Physical

On Prem             On Prem VM

...

Now join your data table to the Sub-Category above, and add the Category as the highest level of breakdown on the Rows of the Matrix.

[moumitadeb]

Thanks for your help. I am able to get the Total count separately. For calculating the difference +/- Prev Month, i have created a measure to find the difference between values for (current month -1) & (current month -2), but it's not working any suggestion will be helpful. 

[ToddChitt]

I suggest you enable the preview feature of Visual Calculations and read up on them here: Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Some highlights:

* Are evaluated only in the scope of the Visual in which they are devined

* Don't have to worry about filter context

* Uses only the data elements available in the Visual

* Has nice WINDOWING functions like PREVIOUS, NEXT, MOVING AVERAGE, etc.

I set up this on a sample visual with dummy numbers. I devined a [Previous Month] Visual Calculation as follows:

Previous Month = PREVIOUS([Sum of QTY Shipped],COLUMNS)

[Sum of QTY Shipped] is an integer field that is the only Values field of my matrix. I used the COLUMNS parameter to tell the PREVIOUS function to go ACROSS THE COLUMNS. 

The first (left most) column of my measure is empty because that column has no 'previous' column, obviously. 

 The matrix visual is on the top half. Right-click and select "New Calculation" and it brings up the matrix on the bottom half and the formula bar. From here, I could do another Visual Calculation using the function "VERSUS PREVIOUS", also with the COLUMNS parameter to see the difference month by month. The top half need not be a matrix visual. You can build calculations on top of other calculations and hide intermmediate ones if needed.

Try it out. 

Cheers!

[ToddChitt]

The raw data looks to be counts, correct? I suggest you UNPIVOT that data in Power Query so that you get data like this:

Date    Category      Count

1/1/24  Azure VMs   123

...

Back in the Model view

Now join the Date column to a Date Dimension that has Month and Year attributes, or better, a single column that displays like you want in the Column Header: "May '23", etc.

Now add the data to a Matrix visual on your report page. Put the Cagetory as the Rows, Month/Year as the columns, and the Count as the aggregation.

Hope that helps.

[moumitadeb]

Thanks for your inputs. I am able to get the data in specified format. But the total is adding all the counts. TOTAL_ON_PREM_SERVERS is the total of On-Prem count and TOTAL SERVER is the overall count. I have to segregate the data as per the screenshot above.