Forum Discussion
Total row in matrix is incorrect
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.
16 Replies
- aj1973
Community Champion
Hi Anonymous
Add ALL
CALCULATE(COUNTA('Orders'[ID]),FILTER(ALL('Orders'),'Orders'[Metric] = "Ins"))
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
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.
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"))
