Forum Discussion

GeorgesKV's avatar
GeorgesKV
Helper I
7 years ago
Solved

Import a PivotTable to PowerBi?

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 ba...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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
)

    sub_of

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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's avatar
    GeorgesKV
    Helper I
    7 years ago

    Did my sample help?

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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
)

      sub_of

       

      Best Regards,
      Dale

      • GeorgesKV's avatar
        GeorgesKV
        Helper I
        7 years ago
        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.