
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
——————————————————————————————————–
Be the first to comment