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

[darrigg]

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. Is there a way to do this?

All of the measures look like this: 

Direct Income = CALCULATE(Sum('Summary Compare Income Statement'[Value]), Filter('Summary Compare Income Statement', 'Summary Compare Income Statement'[ACCT.Profitability] = "Direct Income"))

And then I pulled in the Column that defines the ACTUALS / BUDGET for the measure.

Thank you!!!!

[Anonymous]

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]

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 · Accepted solution]

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.