Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Summarize Measures in a column

Dear all,

I´m having trouble finding a solution to the following:

 

In my company´s budget, we have "three versions" in the database:

1- Planned: have values for all 12 months

2- Actual: have values "up to date" (let´s assume values until month 5)

3- Forecast: future values to be compared with annual plan and future "actuals".

 

Problem: in the Forecast version, I need the past months values to be replaced by what actually happened (could have multiple lines, so needs to be a sum).

I tried to make an example below what I need:

 

Table 1 is what I have. Month 1 to 5 of forecast is not important for me anymore, and needs to be replaced by the sum of "Actual" for each month.

 

ValuesVersionMonth
2,50Actual1
3,00Actual1
1,00Actual2
1,50Actual3
2,00Actual3
2,50Actual3
2,50Actual4
2,00Actual5
3,00Planned1
2,00Planned2
4,00Planned3
3,50Planned4
6,00Planned5
4,30Planned6
2,30Planned7
4,00Planned8
2,30Planned9
2,00Planned10
5,00Planned11
2,30Planned12
3,00Forecast1
2,00Forecast2
4,00Forecast3
3,50Forecast4
6,00Forecast5
12,00Forecast6
13,00Forecast7
13,00Forecast8
11,00Forecast9
14,00Forecast10
15,00Forecast11
11,00Forecast12


 
The table below is what I´m expecting. Notice that the Forecast values of months 1 to 5 are replaced by the sum of each period that already happened, in version "Actual".

ValuesVersionMonthYear
2,50Actual12018
3,00Actual12018
1,00Actual22018
1,50Actual32018
2,00Actual32018
2,50Actual32018
2,50Actual42018
2,00Actual52018
3,00Planned12018
............
    
5,50Forecast12018
1,00Forecast22018
6,00Forecast32018
2,50Forecast42018
2,00Forecast52018
12,00Forecast62018
............


 I do have single measures that work for me, as shown bellow. However, I want to use the Waterfall Chart, and I cannot have different measures. I have to have a single column with different versions, in order to work.

 

Forecast measure

Forecast = 
CALCULATE(
    SUM('Table1'[Values]);
    FILTER(
        'Table1';
        ('Table1'[Version]; 'Table1'[Month])
            IN { 
                ( "Actual"; 1 ); ( "Actual"; 2 ); ( "Actual"; 3 ); ( "Actual"; 4 ); ( "Actual"; 5 );
                ( "Forecast"; 6 ); ( "Forecast"; 7 ); ( "Forecast"; 8 ); ( "Forecast"; 9 ); ( "Forecast"; 10 ); ( "Forecast"; 11 ); ( "Forecast"; 12 )
            }
    )

Planned measure (similar for Actual)

Planned = 
CALCULATE(
	SUM('Table1'[Values]);
	'Table1'[Version] IN { "Planned" }
)

Thank you for the help!

3 Replies

  • Hi Anonymous,

     

    Try this measure:

     

    Measure =
    SWITCH (
        TRUE ();
        CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" )
            = BLANK ()
            && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Forecast" );
        CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" )
            <> BLANK ()
            && MAX ( 'Fact'[Version] ) <> "Planned"; CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Actual" );
        CALCULATE ( SUM ( 'Fact'[Values] ); 'Fact'[Version] = "Planned" )
    )

     

    Should return what you need.

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you MFelix,

      I like your approach, and it would solve my problem if I didn´t need to use the "waterfall chart".

      That means I need this "forecast", but I need to keep my "Planned" values in a different version, so I´m guessing I cannot use a measure in Waterfall Chart. I need to use a column of values in "Y Axis" with "version" as category.

       

      Is there a way to transform this measure formula into a "calculated column" of values, in which "version" would be set to "forecast" for all months?

       

      Thank you once again,

      Eduardo

      • Anonymous's avatar
        Anonymous
        Not applicable

        I found a solution modifying my SQL Query. It works, but I still wish I could find the same solution working directly in PowerBI Desktop modelling with DAX.

         

        Below is the SQL Query that worked, so it might help you find the solution in DAX.

         

        [...]
          CASE 
            WHEN MONTH < 6 AND VERSION = 'FORECAST' THEN (SELECT 
                                                 SUM(ACTUAL.VALUES) AS "Values"
                                             FROM SAP_CO.ACTUAL
                                             WHERE ACTUAL.COSTCENTER = FORECAST.COSTCENTER 
                                             AND ACTUAL.MONTH = FORECAST.MONTH
                                             AND ACTUAL.YEAR = FORECAST.YEAR
                                             AND ACTUAL.CLASSCOST = FORECAST.CLASSCOST
            ELSE
                FORECAST.VALUES AS "Values",
        [...]

        Thank you