Forum Discussion

Danniel's avatar
Danniel
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Replicating excel pivot table in power bi

Hello everyone, I'm trying to replicate an excel pivot table in power bi, basically, I want to have the matrix on the attached image but horizontal instead of vertical, I mean, the months in column names and one line for each of the values, with the line title as the calculation.


Something with this shape:

 

                 jan             feb             mar           apr

item #1  sum(field)   sum(field)  sum(field)  sum(field)

item #2 count(fld)   count(fld)....

 

 

Thank you in advance.

Need Help
  • Royel's avatar
    Royel
    1 year ago

    Hi Danniel You can achieve this by creating a matrix visual in Power BI Desktop 

    Steps: 

    1. Bring Matrix visual in the canvas 

    2. Set Month in the Column and your two numerical columns or measure in the Values 

    3. It will look like this 

    If you are happy with this then you are good to go. Or you can try one more steps 

    4. Enable "Switch values to rows" to get this clean format. 


    Final Results: 

    Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

3 Replies

  • Royel's avatar
    Royel
    Icon for Super User rankSuper User
    1 year ago

    Hi Danniel You can achieve this by creating a matrix visual in Power BI Desktop 

    Steps: 

    1. Bring Matrix visual in the canvas 

    2. Set Month in the Column and your two numerical columns or measure in the Values 

    3. It will look like this 

    If you are happy with this then you are good to go. Or you can try one more steps 

    4. Enable "Switch values to rows" to get this clean format. 


    Final Results: 

    Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

    • Danniel's avatar
      Danniel
      Icon for Advocate II rankAdvocate II
      1 year ago

      Thank you! This is exactly what I was looking for!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Danniel ,
    Thank you for reaching out to Microsoft Fabric community 

    How to Create Date Columns (Month Name and Number)

    Month = FORMAT('CallData'[Date], "mmm")

     

    MonthNum = MONTH('CallData'[Date])

     

    In the Fields pane, select the Month column, then choose Sort by Column and select MonthNum.
     

    Create 2 Measures - 

    For total inbound calls: Total Inbound Calls = SUM('CallData'[Inbound Calls])

     

    For counting services: Count of Services = COUNT('CallData'[Recuento de Servicio])

     

    Set Up a Disconnected Table for Measure Names
    Measure Selector = DATATABLE("MeasureName", STRING, {
        {"Total Inbound Calls"},
        {"Count of Services"}
    })

     

    This table allows you to use measure names as row headers.

     

    Create a Dynamic Measure Based on Selection
    Selected Measure =
    SWITCH(
        SELECTEDVALUE('Measure Selector'[MeasureName]),
        "Total Inbound Calls", [Total Inbound Calls],
        "Count of Services", [Count of Services]
    )

     

    Build the Matrix Visual

    Rows: Measure Selector[MeasureName]

    Columns: CallData[Month]

    Values: Selected Measure


    Please find the attached .pbix file for your reference.

    Regards,
    Sreeteja.