Create calculated row

[_n_MarianLein]

Hi,

I need to create a difference *row* in my report view (table).

The current situation is, I have data like this:

Month   JAN   FEB   MAR
Data1   100   058   160
Data2   112   069   129

I now need to add a row below called "diff" to have this result:

Month   JAN   FEB   MAR
Data1   100    58   160
Data2   112    69   129
Diff     12    11   -31

Problem is, the counts in "Data1" and "Data2" are sums by month, and not directly taken as raw data, meaning in January I'd have 212 rows in the source table, in February 169, and so on...

How can I achieve this?

[Cmcmahan]

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!

[_n_MarianLein]

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...

[v-frfei-msft]

Hi @_n_MarianLein ,

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.

[Cmcmahan · Accepted solution]

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.