Forum Discussion
Trend DAX calculation in Matrix/Column Chart
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?
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.
3 Replies
- amitchandak
Super User
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.
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.
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.
