Forum Discussion
Create calculated row
Ah, I didn't realize Data1 and Data2 were string values in the same column. I set up some sample data in this format:
Month Action Mar Data1 Mar Data1 Mar Data1 Mar Data1 Mar Data1 Mar Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Feb Data2 Feb Data2 Feb Data2 Feb Data2 Feb Data2 So I'm making a few assumptions here, let me know if any are wrong, and I can give a a better-tailored answer. I'm assuming you've put this data into a matrix, which is how you have a COUNT for each one, but are having difficulty getting the difference. Here's the DAX measure to get the difference:
Diff = COUNTAX(FILTER('Table','Table'[Action]="Data1"),[Action])-COUNTAX(FILTER('Table','Table'[Action]="Data2"),[Action])I used COUNTAX so that we can count rows while applying our own filters to 'Table'. This only works if you're hardcoding the values for Data1 and Data2, but this measure will give the difference in the amount of Data1 and Data2 action on any active/selected rows. This lets us evaluate a difference whenever we want, but isn't instantly useful for your situation.
In order to get a matrix to give different totals than a straight sum (which it seems is what you want), you have to get tricky with measures. So I set up a matrix with the Action as the row value, and the Month as the column value. Then I set the values equal to this measure:
MatrixValue = IF(ISFILTERED('Table'[Action]),COUNTROWS('Table'),[Diff])Now this may look overwhelming at first, because there is a LOT going on. The first thing that's happening is DAX is checking if Action is filtered. If it is filtered, that means we are calculating a value within the matrix, so I just have it count the rows. If it is not filtered, that means we are calculating a subtotal, in this case we want a difference so we use the [Diff] measure.
And with that, we get this matrix:
Hopefully that gives you the format you're looking for. If you have any questions about what I just did, let me know.
I'm assuming the calculation by month is a raw sum. If you're doing weird math here, you'll have to adapt.
You can do this by creating a calulated column on your original data. Set it up like so:
Diff = Table[Data1] - Table[Data2]
And then just sum the Diff row like you do the other rows. Here's an example set to show the math works out:
| Data1 | Data2 | Diff | |
| 1-Jan | 3 | 2 | 1 |
| 2-Jan | 2 | 2 | 0 |
| 3-Jan | 1 | 2 | -1 |
| SUM | 6 | 6 | 0 |
From here you can see that it doesn't matter how many rows each month has, PBI will sum them just the same as your original data!
Hi,
Sorry, but I can't figure it out.
My source data is one table that's got 100 lines with ("month" = JAN) and ("Action" = Data1), 112 lines with ("month" = Jan) and ("Action" = Data2), and so on. These are not two separate tables
Can you post a step-by-step instruction how to get the difference between the two rows to work?
Thanks...
Hi Anonymous ,
Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Ah, I didn't realize Data1 and Data2 were string values in the same column. I set up some sample data in this format:
Month Action Mar Data1 Mar Data1 Mar Data1 Mar Data1 Mar Data1 Mar Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Feb Data1 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Mar Data2 Feb Data2 Feb Data2 Feb Data2 Feb Data2 Feb Data2 So I'm making a few assumptions here, let me know if any are wrong, and I can give a a better-tailored answer. I'm assuming you've put this data into a matrix, which is how you have a COUNT for each one, but are having difficulty getting the difference. Here's the DAX measure to get the difference:
Diff = COUNTAX(FILTER('Table','Table'[Action]="Data1"),[Action])-COUNTAX(FILTER('Table','Table'[Action]="Data2"),[Action])I used COUNTAX so that we can count rows while applying our own filters to 'Table'. This only works if you're hardcoding the values for Data1 and Data2, but this measure will give the difference in the amount of Data1 and Data2 action on any active/selected rows. This lets us evaluate a difference whenever we want, but isn't instantly useful for your situation.
In order to get a matrix to give different totals than a straight sum (which it seems is what you want), you have to get tricky with measures. So I set up a matrix with the Action as the row value, and the Month as the column value. Then I set the values equal to this measure:
MatrixValue = IF(ISFILTERED('Table'[Action]),COUNTROWS('Table'),[Diff])Now this may look overwhelming at first, because there is a LOT going on. The first thing that's happening is DAX is checking if Action is filtered. If it is filtered, that means we are calculating a value within the matrix, so I just have it count the rows. If it is not filtered, that means we are calculating a subtotal, in this case we want a difference so we use the [Diff] measure.
And with that, we get this matrix:
Hopefully that gives you the format you're looking for. If you have any questions about what I just did, let me know.
