Matrix Table - Sorting by column, several KPI"s

[M_Chris]

Hi Guys,

as you might know I work with a big and given data model from my company. I would like to sort the KPI's by using another column called 'sorting'. Unfortunately I always get the error message that it cannot get sorted as there is no one to one match between KPI's and sorting column, means I have KPI names in one column and the ranking/sorting I would like to see in my matrix table in another column. However there is another dimension P&L, NCE or NCO statement in the columns. 

If I try to sort my KPI structure by sorting columns it always tells me it is not possible because there is no one to one match between KPI and sorting, means. is there any possibility to sort by using the first sorting number (eg 19 in my table) for profit in general? 

Many thanks!

Chris

KPI	Sorting	Profit loss or NCE	value
Orders	1	PL	100
Revenues	2	PL	90
Profit	3	PL	20
Profit	19	NCE	20
Profit	19	NCE	20
Profit	39	NCO	20
FCF	4	PL	40

[M_Chris]

Many thanks. The point is that my model is based on different sources which includes e.g. several times the KPI Profit on different positions with 20'. In my Key data sheet there should only appear one time the profit. 

Result might just look like:

Orders 100'

Revenues 90'

Profit 20'

FCF 40'

If I try to apply the sorting column to the KPIs which I already have in my model then it gives me the error message that it is not possible as there is no 1-1 relation for KPI to Sorting (as KPI e.g. Profit is in different rows with diffeerent soring numbers due to several sources income statement and balance sheet for example).

[Anonymous · Accepted solution]

Thanks for the reply from Jihwan_Kim , please allow me to provide another insight:

Hi, @M_Chris 

Indeed, as you mentioned, the sorting column must correspond one-to-one with the column being sorted.

Below is a screenshot from the official documentation for your reference:

Here’s a screenshot of the error I encountered:

I recommend using Power Query to regenerate a sorting column. Here’s my proposed solution:

1.First, group the necessary column to retain it and return the maximum value of the sorting column based on the grouping condition.

2.Next, sort the data in ascending order.

3.Insert an index column.

4.Expand the required columns and remove any unnecessary ones.

5.This should allow for proper sorting. Below is the final result, which I hope meets your expectations.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure how your expected outcome looks like, but I tried to create a sample pbix file like below with adding one more column, combined column, and this can be sorted by the sort column.