Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
FelipMark
Helper II
Helper II

How to create matrix with measurements

I have a table like this:

actual.png

 

 

 

 

 

 

I would like to know if it is possible to transform it into a table like this and how to do it. Each KPI, target and Diff. It's a measure I created:

 

goal.png

 

 

 

 

 

 


Maybe it will help, follow the measure I created for the first table:

auxTable = 
UNION(
    SELECTCOLUMNS(
        ADDCOLUMNS(
            VALUES(dCalendario[Data]),
            "Ordem", 1, // Ordem para Vendas
            "Medida", "KPI 1",
            "Valor", [Vendas]
        ),
        "Data", dCalendario[Data],
        "Ordem", [Ordem],
        "Medida", [Medida],
        "Valor", [Valor]
    ),
    SELECTCOLUMNS(
        ADDCOLUMNS(
            VALUES(dCalendario[Data]),
            "Ordem", 2, // Ordem para Meta
            "Medida", "Goal KPI 1",
            "Valor", [Meta]
        ),
        "Data", dCalendario[Data],
        "Ordem", [Ordem],
        "Medida", [Medida],
        "Valor", [Valor]
    )
)




5 REPLIES 5
FelipMark
Helper II
Helper II

I still haven't found the solution, can anyone help me?

v-huijiey-msft
Community Support
Community Support

Hi @FelipMark ,

 

Create measures for your KPI, Goal KPI and Diff respectively.

 

Diff calculates the difference between the KPI and the target.

 

In Power BI Desktop, use the matrix visual.

 

Drag Medida to the "Row" area, drag Year to the "Column" area, and drag the measures (KPI, Goal KPI, and Diff) to the "Value" area of the matrix.

 

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

The problem is that Power BI doesn't allow you to place measurements in the matrix row.... That's why, in the first example, I had to create this auxiliary table

Hi @FelipMark ,

 

Can you provide me with sample data?please remove any sensitive data in advance.

 

Maybe try a calculated column?

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Sample Data: https://drive.google.com/file/d/1SpCLgLhuliXM6GNLdvTVtCYI__Vn0NP2/view?usp=drive_link 

Remembering that for each KPI there is a different goal. How to arrange this in a matrix?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.