Replicating excel pivot table in power bi

[Danniel]

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.

[v-sshirivolu]

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.

[Royel · Accepted solution]

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]

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