Import a PivotTable to PowerBi?

[GeorgesKV]

Hi all,

So we are changing to a unique dashboard instead of creating multiple excel files for each week in various reports, I'm having a problem with one of them.

It's a Capacity Utilization based on Billable and non Billable Hours, one of the colums where I get the data has 2 text values "no" and "yes", so I've created two pivot table, one with the total counting both but showing 2 row for each employee and the "yes" and "no" options with the hours for each one, the colums is year and month, and another Pivot Table where it shows only the billable ones.

With that I have a Colum with Line chart for each employee where Colum is the total utilization (sum of yes and no) and billables and the line is the % of each. -- Here is my problem, because I see that Power Bi doesn't read a specific row to create a chart, but colums, so in the excel I just use the "=" to the pivot sheet with the results for each employee for each month but I can't do that on PowerBi, and if I just import this data for PBI it won't read since it's a bunch of rows and not colums, I was thinking about creating 4 more colums, one with the "sum if yes" one "sum" for everything "% of sum yes" and "% sum".

If anyone wants to help I can send over the excel with the pivots and etctera.

https://imgur.com/a/LXkcE3i  - The problematic one

Regards,

[v-jiascu-msft]

Hi @GeorgesKV,

The link is invalid. It's better to share a sample file. Then we can test and build solutions. 

I think you need several measures in your scenario. Then put it in a table visual, the result will be aggregated by context. Please provide a sample file.

Measure = count('table'[column])

Best Regards,
Dale

[GeorgesKV]

Did my sample help?

[v-jiascu-msft · Accepted solution]

Hi @GeorgesKV,

Please be aware of your data safety. 

If I understand your requirements correctly, it could be done by four measures as follows. Please also check out the demo in the attachment.

sumOfYes =
CALCULATE (
    COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
    PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes"
)

sumOfNo =
CALCULATE (
    COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
    PM__Time_Detail_by_Month_by_Bra[Billable] = "No"
)

%sumOfYes =
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
        PM__Time_Detail_by_Month_by_Bra[Billable] = "Yes"
    ),
    CALCULATE (
        COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
        ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] )
    ),
    0
)

%sumOfNo =
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
        PM__Time_Detail_by_Month_by_Bra[Billable] = "No"
    ),
    CALCULATE (
        COUNTROWS ( 'PM__Time_Detail_by_Month_by_Bra' ),
        ALL ( PM__Time_Detail_by_Month_by_Bra[Billable] )
    ),
    0
)

Best Regards,
Dale

[GeorgesKV]

Thank you. I've removed all the private info, leaving only public and changed info so it's safe. Anyhow the link expired. I will look into this, thank you again.