Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculation - % Complete YTD

Hi,   I need some help to create a measure that gives me a YTD % by month. I've shown the data as I would do it in Excel, but I'm not sure what I need to include in my measure in Power BI.    I h...
  • MauriceMecowe's avatar
    MauriceMecowe
    7 years ago

    Hi Anonymous ,

     

    That's possible but you will need to use the CALCULATE function, and this will result in much more measures. And be aware of the filter context in this case.

     

    Regards,

    Mauricio

MauriceMecowe's avatar
MauriceMecowe
Icon for Resolver II rankResolver II
7 years ago
 

Hi Verity,

 

You can either split your excel sheet in a proper star model, so a table for Actual, Budget, Forecast and one for Date. This is what I prefer. From there it's very simple to continue and reach your goal. Create measures to sum the revenue for each table (Actual, Budget and Forecast). To get the percentage you can divide the measures to get the correct percentage. If you then add the Month from the Date dimension table, it will automatically slice your figure by month.

 

 

Actual = SUM(Actual[Revenue])

Actual Complete = (Actual[Actual]/Budget[Budget])
Budget = SUM(Budget[Revenue])

Hope this helps,

Maurice

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks for the quick response. Is there a solution that would let me keep the data as I have it in one sheet? I have other calculations running off that sheet that I would prefer not to duplicate. 

    • MauriceMecowe's avatar
      MauriceMecowe
      Icon for Resolver II rankResolver II
      7 years ago

      Hi Anonymous ,

       

      That's possible but you will need to use the CALCULATE function, and this will result in much more measures. And be aware of the filter context in this case.

       

      Regards,

      Mauricio