Trend DAX calculation in Matrix/Column Chart

[Anonymous]

Here is my source data:

Here is the data aggregated in a table/bar chart:

What I need to create is a Trend calculation like this:

I can append "Q4 - Trend" to the dataset, but I can't figure out how to populate the values using DAX.  In the actual dataset the user may filter down on results so a formula in excel would not be dynamic.  Any ideas?

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You may create a separate table and two measures for the visual.

1.Create a separate table by entering data. Quarter column is sorted by Sort column.

2.Create two measures.

Margin Measure = 
VAR _count =
    CALCULATE ( COUNT ( 'Table'[Quarter] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
RETURN
    SWITCH (
        MAX ( 'Table (2)'[Quarter] ),
        "Q4-Trend",
            SWITCH (
                _count,
                1,
                    CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q4" ) ) * 3,
                2,
                    CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q4" ) ) * 3 / 2,
                3, CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
            ),
        "Q1", CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q1" ) ),
        "Q2", CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q2" ) ),
        "Q3", CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q3" ) ),
        "Q4", CALCULATE ( SUM ( 'Table'[Margin] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
    )

Sales Measure = 
VAR _count =
    CALCULATE ( COUNT ( 'Table'[Quarter] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
RETURN
    SWITCH (
        MAX ( 'Table (2)'[Quarter] ),
        "Q4-Trend",
            SWITCH (
                _count,
                1,
                    CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q4" ) ) * 3,
                2,
                    CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q4" ) ) * 3 / 2,
                3, CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
            ),
        "Q1", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q1" ) ),
        "Q2", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q2" ) ),
        "Q3", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q3" ) ),
        "Q4", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [Quarter] = "Q4" ) )
    )

3.The visual is this.

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , Where is Q4 trend available. If it is another table, Either you need to append table. Or you need to common Dimension, which includes Q4 and Q4 trend. If it needs to calculated then what is logic behind it.

[Anonymous]

Here is the logic for Q4 Trend:

If we only have one value for Q4 then multiply by 3

If we have 2 values for Q4, SUM Q4, then multiply by 3/2

If we have 3 values for Q4 then set equal to the sum of Q4.

We are trying to see what our total forecast will be for the entire quarter based on the first 1 or 2 months.