Total row in matrix is incorrect

[Anonymous]

I am working with the below calculated measure that is being used for the values of a matrix in Power BI. The totals associated with this matrix are incorrect and I could use some insight as to how best to address it.

Orders: Sales - Measure = CALCULATE(COUNTA('Orders'[ID]),FILTER('Orders','Orders'[Metric] = "Ins"))

I am trying to count the number of IDs found in the Orders table that have a filtered Metric equal to "Ins". 

Any feedback to help resolve this issue would greatly be appreciated.

Thanks!

[Anonymous]

Try this simple fix.  Create a measure to handle the "count" portion of the calculate statement, then place in that measure into the code instead.  This often fixes the total problem.

Count Orders = 'Orders'[ID]

Orders: Sales - Measure = CALCULATE([Count Orders],FILTER('Orders','Orders'[Metric] = "Ins"))

[Anonymous]

@aj1973, I probably should have mentioned that I am using a timestamp field along the x-axis of the matrix. Applying your code makes all the months the same number.

[aj1973]

@Anonymous 

Can you share a sample! it would make it easier for us to help you out

Thanks

[Anonymous]

@aj1973 How would you like me to send a sample? I will probably have to orcustrate a sample since the dataset I am using is confidential work material.

Here is a better summary of the situation. I have a matrix that has a geography heirarchy coming from tableA along the y-axis and I have a month-year timestamp field coming from tableB along the x-axis. The values are coming from the measure depectied in this problem of: Orders: Sales - Measure = CALCULATE(COUNTA('Orders'[ID]),FILTER('Orders','Orders'[Metric] = "Ins")). This formula is looking at tableB which depics order related details. The filter on the Metric field is meant to define what order is a sale order (ie "Ins"). I want to count the number of such order, by ununique ID, in that table and bring it into the matrix that shows the result by geography accoss time. TableA & tableB are joined together by the ID field which exist in both. The total row is not matching up to the totals of the individual rows if I were to add them up manually. This is the issue. The total row is incorrect. Hope this helps. 

[aj1973]

@Anonymous 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150

[Anonymous]

@aj1973 Will this work? Link to Power BI sample

[aj1973]

@Anonymous 

I got your point now, is this what you want to see?

[Anonymous]

@aj1973 Very close. Though I want to be able to drill down to months, I am getting 7,557 as what the total (2-month) total should be, not 7,519. I don't want to see "1"s for each row but rather the actual number of IDs found. Does this make sense?

[aj1973]

@Anonymous 

Here you go

However I don't see how did you get the 7557 count!

[Anonymous]

@aj1973 Let me rephrase my 7,557 count. If I manually (calculator or Excel) add up all the rows, I get 7,557, not 7,519. This is the issue. The total should equal the total.

[aj1973]

Not sure how you are calculating in excel. COUNTA doesn't count the number of rows in the table. If you want to see 7557 then use COUNTROWS INSTEAD.

[aj1973]

Ok before we go any further, you Matrix is some how buggin. You either create a new Matrix or go here

delete that field and re inserted again, the numbers will correct themselves in the Matrix

we talk then.

[Anonymous]

@aj1973 I am not sure what is buggy about it, but I did what you asked and reuploaded.

[Anonymous]

@aj1973 Any thoughts? I still can't figure it out.

[aj1973]

Hi @Anonymous 

any thoughts towards what? can't recall your issue!

[aj1973]

Hi @Anonymous 

Add ALL

CALCULATE(COUNTA('Orders'[ID]),FILTER(ALL('Orders'),'Orders'[Metric] = "Ins"))