Create KPI table with matrix table, with category and sub-category

[Angko2528]

I'm new in Power BI world. I have look into many youtube videos and google without find any solution.
I have this KPI table and i want to convert to power BI report. I have used Matrix visual , it seems good but Im missing Category colume that the KPI data belong to. anyone please help?

KPI table from Excel

Power bi matrix

[v-sdhruv · Accepted solution]

Hi @Angko2528 ,
In matrix only values can be conditionally formatted.

If you wish to have this feature implemented, you can post your idea in ideas forum.
 https://ideas.powerbi.com/forums/265200 .
Hope this helps!

[v-sdhruv]

Hi @Angko2528 ,
You can give conditional formatting as per your requirement. I am sure you wont face any issues with the file I shared.
If you still need some assitance with the logic, can you please share on what conditions you need the conditional formatting so that I can help with you that.
Meanwhile I hope I could help you with category issue?

Thanks!

[Angko2528]

@v-sdhruv 
conditional formating for each KPI for example data 10 shows icon red if value is more than 11000 and data 11 shows icon red if value more than 8000.

[v-sdhruv]

Hi @Angko2528 ,
Please find Pbix file attached for your reference.

[Angko2528]

thank you but it's still  missing option to give conditional formating the values for each KPI

[v-sdhruv]

Hi @Angko2528 ,
1.You can create a calculated column using switch function where it would assign category to each KPI.
2.Then use this new column (category) as rows in matrix visual
3.Then click on the visual and go to format pane where you switch the style to "Tabular"

Below screeenshot for reference-

For calculated column-

More about switch function
Additional links which might help
https://community.fabric.microsoft.com/t5/Desktop/How-to-add-multiple-rows-side-by-side-in-matrix-vi...
I hope this helps!

If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Angko2528]

Hi @v-sdhruv , my table doesnt have a column named ''KPI'' but the KPI data listed as colunms just below 

when i made powerbi report i have slected ''switch values to row groups rather than columns''

so the question is how to add category.

[Angko2528]

in query 

report

[Angko2528]

Can you please send me the pbix file?

so I can see how your query looks like. 
indont have a column name KPI. Each KPI is listed by own column. 

[DataNinja777]

Hi @Angko2528 ,

To display both Category and KPI in the rows of your Matrix visual in Power BI, make sure your data model includes a column for Category that maps each KPI to its correct group (such as A, B, C, etc. in your Excel). Once your data is imported, go to the Matrix visual and drag the Category column into the "Rows" field well, followed by the KPI column just beneath it. This will create a hierarchy where KPIs are listed under their corresponding Category. You can keep your existing "Måned" (Month) field in the Columns well, and all your measure fields like Data1, Data2, etc. should remain in the Values well.

If your dataset doesn't yet have Category values tied to each KPI, you can add them manually in Power Query or create a new table with a relationship. Here's an example using DAX to create a new table that includes categories:

KPI_Category_Map = DATATABLE(
    "KPI", STRING,
    "Category", STRING,
    {
        {"data 1", "A"},
        {"data 2", "B"},
        {"data 3", "B"},
        {"data 4", "B"},
        {"data 5", "C"},
        {"data 6", "C"},
        {"data 7", "D"},
        {"data 8", "D"},
        {"data 9", "D"},
        {"data 10", "E"},
        {"data 11", "E"},
        {"data 12", "E"},
        {"data 13", "E"},
        {"data 14", "E"}
    }
)

After creating this table, establish a relationship between the KPI column in this new table and the existing KPI field in your main data table. Then, use the Category field from the new table in your Matrix visual. The Matrix will now show Categories as the outer row and KPIs nested beneath them, similar to how it's displayed in Excel.

Best regards,

[Angko2528]

It's not possible to establish a relationship between that new table (KPI column) to my existing KPI field as existing KPI field show as more columns (KPI rows)
Existing table

Date	Data 1	Data2	Data 3
	xx	xxx	xxx
	xxx	xxx	xxx
	xx	xx	xx

but that Category table 

KPI	Category
Data 1	A
Data 2	B
Data 3	B

I know i can unpivot the existing table and drag category and KPI in rows but then I will miss possibility to add conditonal formating to the values.
maybe there is another way?