Reporting Actuals & Budget via Matrix

DAX DAX Calculations
1

Hi

I have a fact table – GL Summary, which includes columns,

Posting Date (linked to Dates table)
Amount
Version ID (values are, BUD for Budget, F01 (actuals 1 month forecast 11 months), F02 (actuals 2 months, forecast 10 months). The FY runs Jul to Jul, so once Sep results are finalized, I’ll have F03 (actuals 3 months, forecast 9 months)
GL_ID (linked to GL Chart)

Here are my measures,
GL Amount = SUM ( ‘GL Summary’[Amount] )

Revenue = CALCULATE ( [GL Amount],
FILTER ( ‘GL Chart’, ‘GL Chart’[Level1] = “Revenue”),
FILTER ( ‘GL Summary’, ‘GL Summary’[Version ID] = “F02”)

Revenue BGT = CALCULATE ( [GL Amount],
FILTER ( ‘GL Chart’, ‘GL Chart’[Level1] = “Revenue”),
FILTER ( ‘GL Summary’, ‘GL Summary’[Version ID] = “BUD”)

I have similar measures for expenses, where I’ve replaced ‘GL Chart’[Level1] = “Expenses”

Using a matrix visual I have the following set up,

Month – Jul to Jun
Revenue
Expense
Profit
Revenue BGT
Expense BGT
Profit BGT

The above measures report the correct results if I don’t have a slicer for Version ID.

I’d like to add the slicer so that I can go back and select prior versions – i.e. F01

But when I do, there are no values reported – which I’d expect since the measures have reference to BUD, and F02. Is there a way to achieve this?

Once Sep results are finalized, F03 would appear in the Version_ID which means I’ll have to change the measures for actuals to reference F03 instead of F02.

Thanks
Manoj

2

Try this:

Revenue =
CALCULATE (
    [GL Amount],
    KEEPFILTERS ( 'GL Chart'[Level1] = "Revenue" ),
    KEEPFILTERS ( 'GL Summary'[Version ID] IN { "F01", "F02", "F03" } )
)
3

Thanks Antriksh

I’ve used your suggested measure and this is now reporting total of F01 and F02 for each month if a version isn’t selected in the slicer. If I select a version in the slicer the values are correct.

As an aside when I select either F01 or F02 in the slicer the values against the budget measures do not report. However, if I select BUD in the slicer, the budget values will report but the actuals are blank.

Thanks
Manoj

4

Regarding the first problem you can try this:

Revenue =
CALCULATE (
    [GL Amount],
    KEEPFILTERS ( 'GL Chart'[Level1] = "Revenue" ),
    'GL Summary'[Version ID] IN VALUES ( 'GL Summary'[Version ID] )
)

Regarding the second problem, can you share the file? otherwise everything is dependent on imagination. And I may not think the way you want to.

5

Thanks.
The outcome is the same as the previous measure.
I’ll set up a demo model with demo data and share in due course.
Regards
Manoj

6

Hi again
I’ve attached a copy of demo model.
The first 6 measures are what I originally had.
Revenue Alt is your suggested measure.
I’ve also set up Revenue F01 & Revenue F02 to show the values for each version.
Thanks
ManojSampleFile - Copy.pbix (103.4 KB)

7

@M_K Using this logic you can make modifications to other measures too:
SampleFile - Copy.pbix (104.3 KB)

image
image1245×723 42.5 KB

Revenue =
VAR CurrentSelection =
    SELECTEDVALUE ( 'GL Summary'[Version_ID], "F02" )
VAR Result =
    CALCULATE (
        [GL Amount],
        KEEPFILTERS ( 'GL Chart'[Level1] = "Revenue" ),
        KEEPFILTERS ( 'GL Summary'[Version_ID] = CurrentSelection )
    ) * -1
RETURN
    Result
8

@AntrikshSharma

Prior to putting this question on the forum, I had 2 separate matrix visuals, one to report F02 values for revenue, cost and profit - and the other to report corresponding BUD values by months. I also had set up edit interactions on the version slicer and the BUD visual, so that the BUD values would not change if I selected F02 in the version slicer. The problem with that approach was that the column widths (and months) for the 2 matrix visuals did not align - and I could not find a way to set up column widths. Therefore I brought the BUD measures over to the F02 visual - visually a much better option.

Here is what I’m looking to achieve,

  1. Have one matrix visual to report Act/Fct (F01/F02/etc.), BUD and variance
  2. The BUD values should always remain BUD irrespective of the version slicer selection
  3. The Act/Fct (F01/F02/etc.) values should change as I select the different versions - i.e. if F01 was selected, Act/Fct should report F01 values, and if F02 / F03 / F04, etc. was selected, the corresponding values are reported - and if a version was not selected in the slicer, this should default to F01.

I will also be adding variances for revenue, cost and profit, e.g. [Act/Fct] - [BUD] - so the measures set up at (2) and (3) above need to be able to do this.

Thanks again.
Manoj

9

@AntrikshSharma

I’ve managed to solve the issue.

Steps I took,

  1. Split the GL Summary table up into 2 - via Power Query, duplicate the table, rename this to Bud Summary, and filter version = BUD

  2. Filter GL Summary table to exclude BUD (as version)

  3. Reference the budget measures to the new table,

BUD Amount = SUM ( ‘Bud Summary’[Amount] )
BUD Revenue =
CALCULATE( [BUD Amount], KEEPFILTERS( ‘GL Chart’[Level1] = “Revenue” ) ) * -1

  1. Measures for Act/Fct

Version ID Selected = SELECTEDVALUE ( ‘GL Summary’[Version_ID] )
GL Amount = SUM ( ‘GL Summary’[Amount] )
Revenue =
IF(
[Version ID Selected] = BLANK(),
CALCULATE(
[GL Amount],
KEEPFILTERS( ‘GL Chart’[Level1] = “Revenue” ),
KEEPFILTERS( ‘GL Summary’[Version ID] = “F01” ) * -01
),
CALCULATE(
[GL Amount],
KEEPFILTERS( ‘GL Chart’[Level1] = “Revenue” ),
KEEPFILTERS(
‘GL Summary’[Version ID] IN { “F01”, “F02”, “F03”, “F04”, “F05”, “F06”, “F07”, “F08”, “F09”, “F10”, “F11”, “F12” }
)
)
)

Thanks

1 Like