Count of rows on a Card does not agree with what is rendered.

[BudMan512]

I am having a problem where the count of rows on a Card for a filtered table does not agree with what is actually rendered.  The total count is displayed on a card and the actual rows displayed in a table do not always agree. 

I am looking at a customer’s delivery to determine whether it was on time, late, early or if it was Out Of Gas.  Here is my calculated column used to determine the customer's “Status”. The PCT_AT_FILL is the amount of gas in  the tank before delivery is due.

Status= IF(History[PCT_AT_FILL] <=0, "OOG",

               If(History[QTY]>= 300, "On Time",

                  IF(History[PCT_AT_FILL] >0 && History[PCT_AT_FILL] <20, "Late",

                 IF(History[PCT_AT_FILL] >=20 && History[PCT_AT_FILL] <= 50, "On Time",

                 IF(History[PCT_AT_FILL] > 50, "Early", "On Time")))

The calculated column is then used in a measure to count the rows displayed with the filtered Status.

Early = CALCULATE(COUNTA(History[Status]), History[PCT_AT_FILL] > 50 && History[Status] <>"On Time")

The “Early” count of 47 should be 46 based on the number of rows rendered.  This in turn causes the Total to be off by 1.

The “Early” count seems to be the only one with the problem.

Does anyone have suggestions as to what the problem is?

Thanks,

Bud

[DallasBaba]

@BudMan512I would like to know if the last solution works. If so, Please accept it as a solution so others can reference it.

Thanks

[BudMan512]

@DallasBaba

Thanks for your most recent suggestion.  I think I have partially figured out the problem though.  Sometimes after we make a delivery we need to credit the delivery back and rebill it.  So now our one delivery turns into two deliveries and one offsetting credit.  I ended up adding the Invoice Number to the row context and it rendured the second delivery which agreed with with my count.  However, the SQL query I used to load the data requested 'Qty >= 0', which is why the credit did not show.  My final problem is to figure out a way for the system to only count my original delivery and not the second one used in the  adjustment.

If you have any thoughts on that I would love to hear them.  I very much appreciate your time.

[DallasBaba · Accepted solution]

@BudMan512 To count only the original delivery, you need to distinguish between the two in your calculations. Like using a Filter context for Original Deliveries:

OriginalDeliveries =

CALCULATE(COUNTROWS(History), History[PCT_AT_FILL] > 50 && History[Status] <> "On Time" && History[DeliveryType] = "Original")

Note:  The DeliveryType represents whether a record is an "Original" or an "Adjustment," and It will only count "Original" deliveries.

You can also try the following measures to achieve the same result. 

Early =

CALCULATE(COUNTROWS(History), FILTER(History, History[PCT_AT_FILL] > 50 && History[Status] <> "On Time"))

If those two measures still give you an incorrect count, something else might be affecting the data. 

Another option is to modify your data source query to filter out duplicate deliveries, considering the Invoice Number or another unique identifier. Apply a filter condition in your SQL query to exclude the duplicate deliveries, ensuring you only load the original deliveries into your Power BI model. These will help you count the rows accurately.

Ensuring that your data is properly normalized and relationships between tables are correctly defined is essential. 

Let me know if this works.

Thanks

[BudMan512]

@DallasBaba 

Thanks

[DallasBaba]

@BudMan512 Please change the filter direction on Location & History both.

I hope that solves the problem.
----
Another issue is data redundant because I can spot CUST_ID & BRANCH_NO in 3table in your model. WHY? I would like to see a remaining attribute in your customer and history table to check for data redundant, which can help improve your model performance.

Thanks

[DallasBaba]

@BudMan512 The issue could be related to how the COUNTA function is used in your measure. You should try using the COUNTROWS function instead, as it is better suited for counting rows in a table.

Early = CALCULATE(COUNTROWS(History), History[PCT_AT_FILL] > 50 && History[Status] <>“On Time”)
======OR
Early = COUNTROWS(FILTER(History, History[PCT_AT_FILL] > 50 && History[Status] <> "On Time"))
======OR
Early = CALCULATE(COUNTROWS(History), FILTER(History, History[PCT_AT_FILL] > 50 && History[Status] <> “On Time”))

These should give you an accurate count of the rows displayed with the filtered Status.

Let me know if this helps.

Thanks

[BudMan512]

Hi dallasbabatunde,

@DallasBaba 

Thanks to the reply to my question.  

Your suggestions gave me the same result that I was getting, although I did learn something about how to use the Filter function. It is like there is an invisible row being counted.  I have another branch that overcounts by 15 and the other 10 branches are correct.

The model seems pretty basic.

Do you have any other thoughts on the problem?

Thanks,

Bud