Challenges in Sorting Category Values in Power BI

[mantukumar]

Hello Experts,

I am currently facing challenges in obtaining the correct output as per my requirement in Power BI.

I have a dimension table in the following format:

Column1,Column2,Column3,Column4
samsung1,Samsung,Tier 1,Partnered
samsung2,Samsung,Tier 1,Partnered
sony,Sony,Tier 2,Non-Partner
Google,Google,Tier 2,Partnered
Microsoft,Microsoft,Tier 1,Non-Partner

I have Fact table in the below format

Column1,Sales
samsung1,22
samsung2,10
sony,25
Google,22
Microsoft,9

The required output should be like below

Category,Sales
Samsung,32
Sony,25
Google,22
Microsoft,9
Tier 1,41
Tier 2,47

Total Tier,88
Partnered,54
Non-Partner,34

Total Partnere/Non-Partnered,88

The category values are derived from Column 2, Column 3, and Column 4 based on the following business conditions:

1. The top category values should be taken from Column 2 first, followed by Column 3, and finally from Column 4.

2. The values in Column 2 should be sorted based on sales numbers.

3. Within Column 3, Tier 1 categories should appear first, followed by Tier 2 categories.

4. Categories labeled as "Partnered" should be placed above "Non-Partner" categories, which should be at the bottom.

I have been struggling with this design for the past two days. Can anyone help me achieve the desired output?

[johnbasha33 · Accepted solution]

@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 !!

[Sonu88]

What if we want to have similar table having too many measures for example sales, revenue, backlog, pipeline, cost etc , is it still best way to use summary table or there are any other way as well?

[v-sgandrathi]

Hi @mantukumar,

To handle multiple measures like sales, revenue, backlog, pipeline, and cost in Power BI, you can create individual measures for each metric and then summarize the data based on your categories (Column2, Column3, Column4). Apply sorting logic to ensure the categories are displayed in the required order, such as prioritizing Tier 1 over Tier 2 and Partnered over Non-Partnered. Calculate the total values for different tiers and partner categories to display overall totals. Finally, create a table visual in Power BI using the summarized data, ensuring the sorting logic is applied to maintain the desired order.

This approach helps you efficiently organize and display multiple measures while maintaining the required sorting and aggregation logic.

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

Regards,
Sahasra.

[johnbasha33 · Accepted solution]

@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 !!

[mantukumar]

Hi John,

Thanks for the solution.

Two questions:

1. What if SUM ( FactTable[Sales] ) has more conditions?

2. How to display total for column 2 (Tier1+Tier2) and column3 (Partnere + Non-partnered) values as per the requirement

[v-sgandrathi · Accepted solution]

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.