Challenges in Sorting Category Values in Power BI

mantukumar  Hi, You need to create a calculated table in Power BI that aggregates data based on your business rules and provides sorting as required. Here's how you can approach this:

• Create a summarized table using DAX that aggregates sales based on different category levels (Column2, Column3, Column4).

Apply sorting rules to display the values in the required orde

Create an Aggregated Table Using DAX

Go to Modeling → New Table and enter the following DAX:

CategorySales =
VAR CategorySalesTable =
UNION (
SUMMARIZE ( FactTable, DimTable[Column2], "Sales", SUM ( FactTable[Sales] ) ),
SUMMARIZE ( DimTable, DimTable[Column3], "Sales", SUMX ( RELATEDTABLE ( FactTable ), FactTable[Sales] ) ),
SUMMARIZE ( DimTable, DimTable[Column4], "Sales", SUMX ( RELATEDTABLE ( FactTable ), FactTable[Sales] ) )
)

VAR FinalTable =
ADDCOLUMNS (
CategorySalesTable,
"SortOrder",
SWITCH (
TRUE (),
DimTable[Column3] = "Tier 1", 1, // Tier 1 first
DimTable[Column3] = "Tier 2", 2, // Then Tier 2
DimTable[Column4] = "Partnered", 3, // Partnered next
DimTable[Column4] = "Non-Partner", 4 // Non-Partner last
)
)

RETURN FinalTable

Step 2: Sorting the Table in Power BI

1. Go to Model View in Power BI.

2. Select the Category column in the newly created table.

3. Click on "Sort by Column" and select SortOrder.

4. This ensures that Tier 1 comes first, then Tier 2, followed by Partnered, and finally Non-Partnered.

---

Step 3: Create a Table Visual

1. Add a Table Visual in Power BI.

2. Use the Category column from the CategorySales table.

Use the Sales measure.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

 

 

Hi mantukumar,

Thank you for using Microsoft Fabri cCommunity Forum and Thankyou @johnbasha33 for your response to the query.

Clarification of two questions:

To apply additional conditions, such as filtering by a specific year, region, or product category, you should use the CALCULATE() function inside SUM(). This ensures that the aggregation considers the specified conditions.
Here is an example of the DAX formula:

CALCULATE(

    SUM ( FactTable[Sales] ),

    FactTable[Year] = 2024,

    FactTable[Region] = "USA"

)

To display the total sales for Tiers (Tier1 + Tier2) and Partner Categories (Partnered + Non-Partnered), we need to calculate these values separately and include them in the final table.
Here is an example of the DAX formula:

 

VAR TotalTierSales =

CALCULATE (

    SUM ( FactTable[Sales] ),

    DimTable[Column3] IN { "Tier 1", "Tier 2" }

)

 

VAR TotalPartnerSales =

CALCULATE (

    SUM ( FactTable[Sales] ),

    DimTable[Column4] IN { "Partnered", "Non-Partner" }

)

 

VAR FinalTable =

UNION (

    CategorySalesTable,

    ROW ( "Category", "Total Tier", "Sales", TotalTierSales ),

    ROW ( "Category", "Total Partnered/Non-Partnered", "Sales", TotalPartnerSales )

)

 

RETURN FinalTable

 

Hope this helps for your clarification. Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Thank you.