Delta as Row Total in Matrix - Direct Query

[bensiqc]

First of all, I'm using DirectQuery method so creating any tables, etc., isn't an option for me.

I have a simple matrix visual containing a hierarchy. I have carrier and Month in the rows, year as the column, and a count for values. Rather than a sum total on the far right, I want to show a delta between the two columns. So, for example, the total for January would show "-370,455". Most methods I've found for doing this require creating a helper table, etc. I'm hoping there's a relatively simple way to do this using Direct Query.

[Anonymous]

Hi @bensiqc,

You can refer to the following link to use ISINSCOPE function to check the current row context level and replace the calculation format if it on the total level.

Use IsInScope to get the right hierarchy level in DAX - Kasper On BI

Then you can get the min and max year from the current aggregations and use them as condition in variables to get correspond results and use in the difference calculations.

Regards,

Xiaoxin Sheng