Forum Discussion

M_Chris's avatar
M_Chris
Frequent Visitor
1 year ago
Solved

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: 

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):

 

Many thanks for your support. 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

    Create calculated table:

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

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

    Joins the relationship between two tables.

    Result:

     

    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.

2 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    1 year ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    Create calculated table:

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

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

    Joins the relationship between two tables.

    Result:

     

    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.