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 va...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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
8 years ago

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