Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with calculations

I need to create a line chart with monthl by month figures.  I get a report each month that I need to complete multiple steps in Excel to create this chart.  I want to be able to create this in Power...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

    I did this in two measures. Hope this helps!

    UnitTotal = SUMX('Table', 'Table'[L25]+'Table'[L27])

Monthly Total = 
VAR PrevMonthUnitTotal = CALCULATE([UnitTotal],
                         PREVIOUSMONTH('Table'[Report Date])
                         )
Return
IF(SELECTEDVALUE('Table'[This Month]) = "October",
    [UnitTotal],
    [UnitTotal]-PrevMonthUnitTotal
    
 )

     

    ~Kim

Anonymous's avatar
Anonymous
Not applicable
6 years ago

A little tricky to do this in the abstract but here is my approach

 

- Make a calculated column to get your total (L25 + L27) and call it, for example, Total

- Make a measure for total sales, like Sum(Total). It doesn't matter if you have multiple units you want to split out, as you can do that using row context in your table later

- Lastly I'm not sure you need to mess around with YTD calculations - if you place the measure in a table with Date and Unit you should get what you are after, assuming that your data is not cumulative, which it doesn't look like it is?

 

Hope this helps!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you for such a quick response.  Unfortunately, my data is cumulative.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Not a problem!

       

      We'll just need to make a extra measures. First we want the YTD total, but with October being the start so make a measure like this:

       

      YTD = TOTALYTD(SUM([Total], [Date], All ([Date]), "01/10")

       

      This will give you a YTD measure. Next we need to figure out the YTD for the previous month

       

      PYTD = TOTALYTD(SUM([Total], [Date], PREVIOUSMONTH([Date]), "01/10")

       

      Now make a final measure which is just the first measure - the second measure

       

      Final = [YTD] - [PYTD]

       

      Pop that in your table with your unit and your date and it should be fine 🙂