CODEWITHSUNDEEP
powerbidax
Moeez
Moeez

Reputation: 478

Power BI - Matrix Count Blank Rows

I have created a matrix having data of salesman and their chemist visited.

enter image description here

I have added a count formula

Customer Count = DISTINCTCOUNTNOBLANK('Source Data'[CustomerNameFull])

The total in the above matrix is of the salesman who visited the chemist, along with that I want to calculate the total of chemist that is not visited by a salesman. i.e. the count of the blank. The blank count should be shown along with the total value.

Source File: BI SAS

Expected Output

Below is the expected output that I want. The zero total is the distinct count

enter image description here

Any help would be highly appreciated.

Upvotes: 5

Views: 2359

Answers (2)

Marcus
Marcus

Reputation: 4005

With the expected result in place I propose the following measures. One to count the distinct unvisited chemists for the selected salesman/salesmen:

Unvisited Chemists := 
VAR _customerCount = CALCULATE ( DISTINCTCOUNTNOBLANK ( 'Source Data'[CustomerNameFull] ) , ALLSELECTED ( Dates ) )
VAR _visitedCount = [Customer Count]
RETURN _customerCount - _visitedCount

And one to format the matrix:

Customer Count w/ Unvisited = 
IF ( [Customer Count] <> BLANK () ,
    SWITCH ( 
        TRUE (), 
        NOT ISINSCOPE ( 'Source Data'[CustomerNameFull] ) && ISINSCOPE ( Dates[Month] )  ,
        [Customer Count] & " (" & [Unvisited Chemists] & ")" ,
        FORMAT ( [Customer Count] , "#" )
    )
)

Please note that here I am using a date table instead of using auto date/time on the INVDATE column. This is considered best practice anyway. Remember to set the correct sort column, see guide here: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

I have used a simple date table:

Dates = 
ADDCOLUMNS ( 
    CALENDARAUTO () , 
    "Year" , YEAR ( [Date] ) ,
    "MonthNo" , MONTH ( [Date] ) ,
    "Month" , FORMAT ( [Date] , "MMMM" )
)

The data model looks like this:

Data model

The result looks like this:

Resulting matrix visual

Upvotes: 2

AntrikshSharma
AntrikshSharma

Reputation: 661

You can try something like this:

VAR AllChemists = 
    CALCULATETABLE ( 
        VALUES ( Table[Chemist] ), 
        REMOVEFILTERS ( Table[Chemist] ) 
    )
VAR VisitedChemists = 
    FILTER ( 
        AllChemists, 
        CALCULATE ( 
            DISTINCTCOUNT ( 'Source Data'[CustomerNameFull]  ) 
        ) > 0
    )
VAR RemainingChemists = 
    EXCEPT ( AllChemists, VisitedChemists )
VAR Result = 
    COUNTROWS ( 
        FILTER ( 
            RemainingChemists,
            Table[Chemist] IN VALUES ( Table[Chemist] )
        )
    )
RETURN
    Result

Upvotes: 0

Related Questions