Forum Discussion

darrigg's avatar
darrigg
Frequent Visitor
4 years ago
Solved

Get Difference and % Difference In Matrix Table when values are Measures

Hi - I am trying to make 2 extra columns in the matrix below that calculate the difference and then the % difference of each one of these measures.   I created a matrix and swtiched values to rows....
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi darrigg ,

     

    As I mentioned above, I think [Actual vs Budget] in matrix column should be a column with only two values "Actual" and "Budget". So you add multiple measures or columns into value fields and matrix will expand into only two columns "Actual" and "Budget". 

    You can add a column with more values like "Actual", "Budget","Diff" and "Diff%" into Matrix column field.

    Then create measures by switch function. For example, [Direct Income] measure should look like as below.

    Direct Income =
VAR _Acutal = [Actual for Direct Income]
VAR _Budget = [Budget for Direct Income]
VAR _DIFF = [Diff for Direct Income]
VAR _DIFF_PERCENT = [Diff % for Direct Income]
RETURN
    SWITCH (
        SELECTEDVALUE ( [ColumnField] ),
        "Actual", _Acutal,
        "Budget", _Budget,
        "DIFF", _DIFF,
        "DIFF%", _DIFF_PERCENT
    )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi darrigg ,

 

I suggest you to add 'Summary Compare Income Statement'[ACCT.Profitability] column into matrix rows field. 

And then calculate [Actual] and  [Budget] separately.

Acutal =
CALCULATE (
    SUM ( 'Summary Compare Income Statement'[Value] ),
    FILTER (
        'Summary Compare Income Statement',
        'Summary Compare Income Statement'[Actual vs Budget] = "Actual"
    )
)
Acutal =
CALCULATE (
    SUM ( 'Summary Compare Income Statement'[Value] ),
    FILTER (
        'Summary Compare Income Statement',
        'Summary Compare Income Statement'[Actual vs Budget] = "Budget"
    )
)
Diff = [Actual] - [Budget]
Diff % = DIVIDE([Diff],[Budget])

Add these measures into matrix value field.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • darrigg's avatar
    darrigg
    Frequent Visitor
    4 years ago

    Thanks, Rico.

     

    The reason why I have it set up like this is because Net Profit, Taxes, and net interest income are calculated off of the measures. So If i pull in the profitability into the, it would work, but i would need to sort and organize and have calculated measures inbetween the columns.

     

     

    Although not the prettiest, I created additional measures to calculate the different in a long measure by using CALCULATE and filtering for actuals vs budget and then finding the difference. I renamed each row header to be " " so there is nothing there (unless there is a better way to do this?).

     

    I will be working on creating a similar format for the % difference.

     

    Any other ideas to make it more in one table? Or is my janky workaround fine you think?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi darrigg ,

       

      As I mentioned above, I think [Actual vs Budget] in matrix column should be a column with only two values "Actual" and "Budget". So you add multiple measures or columns into value fields and matrix will expand into only two columns "Actual" and "Budget". 

      You can add a column with more values like "Actual", "Budget","Diff" and "Diff%" into Matrix column field.

      Then create measures by switch function. For example, [Direct Income] measure should look like as below.

      Direct Income =
VAR _Acutal = [Actual for Direct Income]
VAR _Budget = [Budget for Direct Income]
VAR _DIFF = [Diff for Direct Income]
VAR _DIFF_PERCENT = [Diff % for Direct Income]
RETURN
    SWITCH (
        SELECTEDVALUE ( [ColumnField] ),
        "Actual", _Acutal,
        "Budget", _Budget,
        "DIFF", _DIFF,
        "DIFF%", _DIFF_PERCENT
    )

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.