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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
M_Chris
Frequent Visitor

Margin in % in a Matrix table by adding a sorting column

Hi Guys, 

 

I need your support. As example I created a very short matrix table which looks like following: 

M_Chris_0-1731520141173.png

Let's please focus on TFY24 where I wanted to have a separate measure for that fiscal year. The value I created in DAX as following including showing the correct Profit% margin:

TFY24 =
VAR CurrentKPI24 = SELECTEDVALUE(V_TBL_ALL_DATA_EPM[KPI])
VAR CurrentREV = CALCULATE(SUM(V_TBL_ALL_DATA_EPM[Value]),V_TBL_ALL_DATA_EPM[KPI]="Revenue",V_TBL_ALL_DATA_EPM[Data Type]="Act FY24")
VAR CurrentProfit = CALCULATE(SUM(V_TBL_ALL_DATA_EPM[Value]),V_TBL_ALL_DATA_EPM[KPI]="Profit",V_TBL_ALL_DATA_EPM[Data Type]="Act FY24")
RETURN SWITCH(TRUE(),
    CurrentKPI24 = "Profit %", FORMAT(
        DIVIDE(CurrentProfit, CurrentREV),"0.0%"),
CALCULATE(SUM(V_TBL_ALL_DATA_EPM[Value]),V_TBL_ALL_DATA_EPM[Data Type] = "Act FY24")
)
//END!!!

 

so far so good. Now I added another column in front of my KPI column. It is a "sorting" column in order to sort the KPIs in a very efficient approach which I like. However and unfortunately suddenly my Profit margin is disappearing. Currently I do not understand why I would have to adjust my TFY24 formula as I thought it is related to the KPI dimension but not to any sorting dimension. Do you have any idea. Looks like this now (sorting is correct and my idea was to hide the column in the end):

M_Chris_1-1731520476148.png

 

Many thanks for your support. 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for the reply from AntrikshSharma , please allow me to provide another insight: 
Hi  @M_Chris ,

Power BI default sorting is alphabetical, if your data [KPI] has multiple identical values in a table or there is a circular dependency problem, you may consider creating a new table to customize the sorting.

vyangliumsft_0-1731552416229.png

Create calculated table:

SUMMARIZE(
    'Table',[KPI],"Rank",SUMX('Table',[Value]))

vyangliumsft_1-1731552416231.png

Select [KPI] – Column tools – Sort by  column – [Rank].

vyangliumsft_2-1731552442368.png

Joins the relationship between two tables.

vyangliumsft_3-1731552442369.png

Result:

vyangliumsft_4-1731552456538.png

 

Best Regards,

Liu Yang

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thanks for the reply from AntrikshSharma , please allow me to provide another insight: 
Hi  @M_Chris ,

Power BI default sorting is alphabetical, if your data [KPI] has multiple identical values in a table or there is a circular dependency problem, you may consider creating a new table to customize the sorting.

vyangliumsft_0-1731552416229.png

Create calculated table:

SUMMARIZE(
    'Table',[KPI],"Rank",SUMX('Table',[Value]))

vyangliumsft_1-1731552416231.png

Select [KPI] – Column tools – Sort by  column – [Rank].

vyangliumsft_2-1731552442368.png

Joins the relationship between two tables.

vyangliumsft_3-1731552442369.png

Result:

vyangliumsft_4-1731552456538.png

 

Best Regards,

Liu Yang

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

AntrikshSharma
Super User
Super User

@M_Chris After RETURN return each variable and see what it returns with and without the sorting column, focus on where it deviates, it could be that SELECTEDVALUE is returning BLANK

 

RankingCM_2 column is in the same table as the KPI column?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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