How to Summarize and Rank the Data based on Count in Power BI | Power BI Blog


How to create Summary Tables and Rank the Data by Count using DAX in Power BI
Scenario :
Suppose we have a Sales Order details like the Count of OrderIDs by Country Name, Customer Name , Product Name as shown below :




Now we will generate a Summary Table, to show Count of OrderIDs by Customer, and then we Rank the data based on the Count of OrderIDs, using the below DAX Expressions :


SUMMARIZECOLUMNS Method :
SummarizeColRank = 
VAR vSummaryTable=SUMMARIZECOLUMNS(SalesDetails[CountryName],
FILTER(VALUES(SalesDetails[CountryName]),SalesDetails[CountryName]<>BLANK()),
“OrderCount”, CALCULATE(COUNT(SalesDetails[OrderID])))
RETURN
TOPN(10,
ADDCOLUMNS(vSummaryTable,”RankN”, RANKX(vSummaryTable, [OrderCount],,DESC,Dense)),
[RankN],ASC)

Notes :
Using this Method, we can add our desired no.of GroupBy Columns(Eg: [CountryName]) in the SUMMARIZECOLUMNS section.
In FILTER section, I have Filtered out the Blank() values, so that Blank() Country grouping will not show in the Summary Table.
We can use TOPN to select the desired no.of Top (Eg: TOPN(5)) results based on the Rank. Here, I have used N=10 to display all as I already know the result.


Result :





ADDCOLUMNS Method :
SummarizeAddColRank = 
VAR vSummaryTable=ADDCOLUMNS(FILTER(VALUES(SalesDetails[CountryName]),
SalesDetails[CountryName]<>BLANK()),
“OrderCount”, CALCULATE(COUNT(SalesDetails[OrderID])))
RETURN
TOPN(10,
ADDCOLUMNS(vSummaryTable,”RankN”, RANKX(vSummaryTable, [OrderCount],,DESC,Dense)),
[RankN],ASC)


Result :


——————————————————————————————————– 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

——————————————————————————————————–





Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*