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
MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

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