Forum Discussion

acs's avatar
acs
Regular Visitor
9 years ago
Solved

A conditional MAX?

Hello - have been learning a lot by reading these forums and all the helpful posts on here. Now I've run up against a problem I cannot find the answer for and hoping someone can help.   I have data...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    acs,

     

    Could you give us a expected chart as the sample? To get the vaule you want, we can use ALLEXPECT function.
    TypeTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type]))
    ProjectTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type],Table1[Project]))
    VersionTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type],Table1[Project],Table1[Version]))

     

    Regards,

    Charlie Liao

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

I may not totally understand what you are going for, but if I understand correctly, couldn't you do something like:

 

TotalSales = CALCULATE(SUM([Sales]),FILTER(BMX,[Month]=MAX([Month])))
  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    My suggestion would be to normalize your source data, either at the real source or in Power Query:

    1. Put the colour in a separate column.
    2. De-accumulate Sales figures.

    In your example: would the 103 Version 2 Sales include the 98 Version 1 Sales?

     

    Does accumulation start each year in January?

     

    What is your format for Month: text or a date (e.g. first of the month)? This require to de-accumulate the Sales correctly.

    • acs's avatar
      acs
      Regular Visitor
      9 years ago

       Appreciate the swift replies. Going to try and work my way through both suggestions and see if I can get either to work.

       

      Meanwhile, if it helps:

       

      "In your example: would the 103 Version 2 Sales include the 98 Version 1 Sales?"

      No.

       

      "Does accumulation start each year in January?"

      Yes (well, not January but at the beginning of our financial year, which is August - so totals run up to 31st July, then reset).