Thursday, December 26, 2019

How to show the Slicer List with Count of Items in Power BI

How to display the Count of Items in the Slicer List in Power BI
Scenario :
Suppose we have a dataset that shows the Sales information by Product as shown below :


Now our requirement is to show the Count of each Product in the Slicer as shown below.

We can achieve this by using the Calculated Table created from below DAX Expressions :
Summarize Method :
_ProdSummary = SUMMARIZE(SalesByCustomer, SalesByCustomer[ProdName],
"_ProdCount",COUNT(SalesByCustomer[ProdName])+ COUNTBLANK(SalesByCustomer[ProdName]),
"_ProdNameCount",CALCULATE(IF(ISBLANK(VALUES(SalesByCustomer[ProdName])),
"Blank()",VALUES(SalesByCustomer[ProdName])) " (" & COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName]))&")")

AddColumns Method :
_ProdSummary = ADDCOLUMNS(SUMMARIZECOLUMNS(SalesByCustomer[ProdName],
"_ProdCount",COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName])),
"_ProdNameCount",CALCULATE(IF(ISBLANK(VALUES(SalesByCustomer[ProdName])),
"Blank()",VALUES(SalesByCustomer[ProdName])) & ("& COUNT(SalesByCustomer[ProdName])+
COUNTBLANK(SalesByCustomer[ProdName])&")"))

here, _ProdSummary is the Calculated Table ;
SalesByCustomer is the source Table from which we are Summarizing the data by the Column [ProdName] and finding the Count(including Blanks).

Result :



Notes :
-------------------------------------------

To effectively use this Slicer, we have to Map this Calculated Table with the Source Table as shown in below Model :

Since there were Blank() values in the Source table "SalesByCustomer", we are not able to establish the Many-One relation ship with Summary table. So that we chosen Many-Many relationship in this example.

Now we can validate the Data slicing , which will work as expected, shown below :
-------------------------------------------------------------------------------------------------------- 
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.