Recreating Excel Table

[larryneal]

Grettings, 

I am looking to recreate the following table from Excel inside of Power BI. 

This is a single staff person with a summary of time based on specific catories over the course of a year (fiscal). The data exist as individual transactions in a SQL DB, including the following fields: date, staff, hours, timecategory. I have a date table established. 

I'm pretty early in my PowerBI development. So, if you can point me the right direction I would greatly appreciate it. I've found a couple of post but nothing that is quite the same. 

Thanks,

Larry

[v-lionel-msft]

Hi @larryneal ,

Do you want to make the matrix in EXCEL?

And your data source is from SQL DB?

Maybe you can use PowerQuery to connect the databases?

https://dev.mysql.com/downloads/connector/net/ 

Or use ODBC to connect the databases.

https://dev.mysql.com/downloads/connector/odbc/ 

After getting the data, you can create PivotTable to create a matrix.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Can you share the Excel? You could use a matrix visualization for that.

[larryneal]

The Excel file does not pull any data. It only has very simple calculations such as summing across the row. 

[Anonymous]

OK so the Excel snag you showed is just an example of what you'd like to see. 

Have you been able to connect Power BI to the SQL database?

[larryneal]

Correct. 

Yes, I have PBI connected to the DB and have been performing other reports within PBI. I have some measures created with some of these values (i.e. TotalPTO, PriorYearPTO, NetHours, PriorYearNetHours, etc.). Where I am struggling is how do I create rows that match excel and then pull in the columns. I'm assuming the columns would be my measures/calculated columns. 

[Anonymous]

Sounds like you want to create a table with a Financial Performance column and do lookups to bring in the metrics shown in the remaining columns of the Excel snag. Is that correct?

[larryneal]

That sounds like what I'm looking for. 

I would also have a slicer that would allow me to select the person/staff that I was interested in looking at these values for. 

[Anonymous]

I believe one would need to see your data/data model to guide further. Without it, all I can recommend is going to the Modeling tab, "New Table" to create a table, and then doing some research on the myriad of DAX functions available to bring in the data you need.