I am using the Power KPI Matrix visual with column-based data, and I'm having an issue. To group the categories together, the visual wants an ID associated with each row, but since each row has all of the categories in it, I don't know what ID to put for the rows. The sample file has an ID associated with each row, but I don't see how to correctly connect it in my file. Can anyone help?
Sample of my data (Excel file)
(Goal ID, Goal, Category)
2, goalA, categoryA
2, goalA, categoryB
3, goalB, categoryC
4, goalC, categoryD
(Date, categoryAvalue, categoryBvalue, categoryCvalue, categoryDvalue)
2018, 5, 10, 7, 9
2019, 6, 12, 6, 9
So I need to associate the Goal ID fromm the Categories table to the rows in the Data table. But each row has a value for categoryA, categoryB, categoryC, and categoryD. So I can't assign one Goal ID to the rows in Data because it has a vaule for every Goal ID in the Categories table.
Even in the example data given on the app page (here) doesn't make sense, and it's the same problem. If you look at the table Column_Based_Metrics, and take the row from 9/1/2017, the ID column has the value 4, which would be the KPI Transactions in the Col_Links table. But the row has values for the KPIs Total Units (ID 1), Premium Units (ID 2), Gross Revenue (ID 3), Transactions (ID 4), Active Customers (ID 5), Avg Price (ID 6), Returns (ID 7), and Satisfaction (ID 8). So how is it determined that this row belongs with the ID 4 when it has values for every KPI? It seems as though every ID should be in that column since each has a value in the row.
Maybe I'm missing something?
Goal ID column must contain values from 1 to 13 in your case. This column must be in Categories table and Scorecard data table.
After that, you must open Relationship manager to make a relation between two table by using Goal ID as a key column.
As a reference you can use Sample report.
Microsoft Power BI Custom Visuals
I'm having a similar issue that I'm trying to get my head around this.
If using column based data where the column headers relate to the categories, how can creating an ID column in this table relate to a categories in a second table?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.