Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using minimum function after aggregation

Hi all. 

 

I am trying to apply a minimum function after aggregation. Meaning that PBI should not look in raw data for the minimum, but instead it should find the minimum after aggregation.

 

My minimum function is conditional. It is about finding the "point 0" for a time series (paybacktime).

 

It would seem that this is a relative frequent occuring task, so wondering if someone have a nice recipe for this? 🙂

 

Best Regards,

Torben

Need Help

7 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Not sure if understand the requirement, it would be a lot easier if you would provide a data sample, explain your scenario and provide an expected outcome.

     

    but you can try to use MAXX aggregating over an attribute, like for example.

     

    max sales by color = MAXX( VALUES( table[color] ), CALCULATE( SUM( table[sales amt] ) ) )

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your input.
      Here is a visualization of my problem, hope it's helpful. 

       

      Top table: here is my raw data, split by country and device. The columns "000", "001", .. represent the accumulated customer value.

      Middle table: a PBI user have aggregated the values on country, perhaps in a excel connection. 

      Bottom table: this is the output im looking for (it does not need to show the relative month, just the break-even month).

       

      I tried the following: payback_month = CALCULATE(MIN(table[relative_month]),cashflow[value_acc] > 0). 

      This returns the minimum value, but for the raw/non-aggregated data! 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Exactly! Except that the "summarize" part should be dynamic. We have lot's of users on an Excel-connection that will aggregate data in all kind of ways. 

       

      Btw: i haven't examined how these kind of functions works in an Excel-connection. 

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        First go to query editor>select column 000,001,002,003>choose "unpivot columns";

        Then create 2 measures as below:

         

        _total = SUMX(FILTER(ALL('Table (2)'),'Table (2)'[Attribute]=MAX('Table (2)'[Attribute])&&'Table (2)'[Country]=MAX('Table'[Country])),'Table (2)'[Value])
        payback periods = 
var a=MINX(FILTER(ALL('Table (2)'),'Table (2)'[_total]>0),'Table (2)'[_total])
Return
CALCULATE(MAX('Table (2)'[Attribute]),FILTER('Table (2)','Table (2)'[_total]=a))

         

        Finally you will see:

        For the related .pbix file, pls click here.

         


        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!