- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Summarize Measures in a column

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

Summarize Measures in a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-18-2018
02:45 PM

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.

Values | Version | Month |

2,50 | Actual | 1 |

3,00 | Actual | 1 |

1,00 | Actual | 2 |

1,50 | Actual | 3 |

2,00 | Actual | 3 |

2,50 | Actual | 3 |

2,50 | Actual | 4 |

2,00 | Actual | 5 |

3,00 | Planned | 1 |

2,00 | Planned | 2 |

4,00 | Planned | 3 |

3,50 | Planned | 4 |

6,00 | Planned | 5 |

4,30 | Planned | 6 |

2,30 | Planned | 7 |

4,00 | Planned | 8 |

2,30 | Planned | 9 |

2,00 | Planned | 10 |

5,00 | Planned | 11 |

2,30 | Planned | 12 |

3,00 | Forecast | 1 |

2,00 | Forecast | 2 |

4,00 | Forecast | 3 |

3,50 | Forecast | 4 |

6,00 | Forecast | 5 |

12,00 | Forecast | 6 |

13,00 | Forecast | 7 |

13,00 | Forecast | 8 |

11,00 | Forecast | 9 |

14,00 | Forecast | 10 |

15,00 | Forecast | 11 |

11,00 | Forecast | 12 |

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".

Values | Version | Month | Year |

2,50 | Actual | 1 | 2018 |

3,00 | Actual | 1 | 2018 |

1,00 | Actual | 2 | 2018 |

1,50 | Actual | 3 | 2018 |

2,00 | Actual | 3 | 2018 |

2,50 | Actual | 3 | 2018 |

2,50 | Actual | 4 | 2018 |

2,00 | Actual | 5 | 2018 |

3,00 | Planned | 1 | 2018 |

... | ... | ... | ... |

5,50 | Forecast | 1 | 2018 |

1,00 | Forecast | 2 | 2018 |

6,00 | Forecast | 3 | 2018 |

2,50 | Forecast | 4 | 2018 |

2,00 | Forecast | 5 | 2018 |

12,00 | Forecast | 6 | 2018 |

... | ... | ... | ... |

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
##

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

MFelix

Super User

Re: Summarize Measures in a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-18-2018
03:25 PM

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

Proud to be a Datanaut!

Anonymous

Not applicable

Re: Summarize Measures in a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018
04:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018
05:57 AM

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