Forum Discussion

bensiqc's avatar
bensiqc
Helper I
3 years ago

Delta as Row Total in Matrix - Direct Query

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.

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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