Customer count with blank brokerage amount

[VIrajMP]

Hi,

I am trying to get a count of customers who have given brokerage during the last month regardless of the customers giving brokerage in the current month. I have created 2 measures (TM Net Brokerage and LM Net Brokerage) as given below.

TM Net Brokerage =

IF(ISBLANK([Net Brokerage]),0,

           CALCULATE([Net Brokerage],

                 FILTER(Values(ActualswithTargetIncome[Partykey]),

                         IF([Net Brokerage] < 5000

                                 && [Net Brokerage LM] >= 25000, [Net Brokerage],BLANK())))

)

LM Net Brokerage =

IF(ISBLANK([Net Brokerage LM]),BLANK(),

                  CALCULATE([Net Brokerage LM],

                         FILTER(Values(Customer[CustomerNameCode]),

                                IF([Net Brokerage] < 5000

                                                && [Net Brokerage LM] >= 25000, [Net Brokerage LM],BLANK())))

)

NoofCustomers =

IF(ISBLANK([Net Brokerage]),1,

        COUNTROWS(

                       FILTER (

                         ADDCOLUMNS (

                                     VALUES ( ActualswithTargetIncome[Partykey] ),

                                          "NetBrok", CALCULATE ( [Net Brokerage] ),

                                          "NetBrokLM", CALCULATE([Net Brokerage LM])),

                                                           [NetBrok] < 5000  && [NetBrokLM] >= 25000 )

                               )

)

The visual below gives me the desired result when viewed by pcode, however I am unable to get the correct No of customers (when placed in a card visual) which in this case should be 8. I guess it ignores the blank cells (which I have replaced by 0 in the TM Net Brokerage) and counts only rows where the brokerage exists.

I would like to have the count as 8, i.e. include the blanks, Will appreciate, if anyone could help me out with the dax measure to get the desired result.

Thanks and Regards

Viraj

[v-lionel-msft]

Hi @VIrajMP ,

Please refer to the links.

Dealing with Measure Totals 

Power BI: Totals Incorrect 

If the problem is still not resolved, please share the .pbix file or sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@VIrajMP , Try measures like

NoofCustomers = countx(values(Table[pcode]), if([TM Net Brokerage] =0 || isblank([TM Net Brokerage]) ,0,1))

NoofCustomers = countx(values(Table[customer ID]), if([TM Net Brokerage] =0 || isblank([TM Net Brokerage]) ,0,1))