cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 3
Highlighted
Super User
Super User

Re: Summarize Measures in a column

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

Re: Summarize Measures in a column

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
Not applicable

Re: Summarize Measures in a column

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