Friday, January 3, 2020

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

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.