Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Changing aggregation condition based on condition

Hi all

I’m new to PowerBI and struggling with a puzzle on "changing the aggregation function depending on a condition" (I think)… Google hasn’t helped me solve it. To reduce it to a “simple” example… I have source data made up of two columns: ID number and score, and want to summarize in a table with three columns which are ID number, score, occurrences.

There are three types of entry:

  • unique IDs where I will record ID, score and occurrence would be 1
  • anonymized IDs (ID = ‘anon’) where I will have the ‘anon’ ID, the sum of the scores and the number of occurrences
  • duplicated IDs (not ‘anon’) where I want to record the ID, maximum score and number of occurrences

I started with something like
Summary = SUMMARIZECOLUMNS(‘Source’[ID], SUM([Score], “Count”, COUNT(‘Source’[ID]))
but failed in my attempt to change the SUM to be a conditional expression that was either MAX if ID<>”anon” or SUM if ID=”anon”.
I’d be grateful for any suggestions
Thanks
Andrew

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    Would you please try the following dax:

     

     

    Table 2 = SUMMARIZE('Table','Table'[ID],"Count",COUNT('Table'[ID]),"total",IF('Table'[ID] = "anon",SUM('Table'[Score]),MAX('Table'[Score])))

     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous create two measure and in any visualization, add id and these measures and it should work

     

    Total Sum = SUM ( Table]Column] )

Max Value = MAX ( Table[Column] )

Count = COUNTROWS ( Table )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the very quick reply parry2k! 

      I think your solution is giving me two results per ID, a max and a sum, and I was looking for a single one.

      I did work out a solution by splitting the problem - create a set of results for the anonymized data which sums scores, and a set of results for the non-anonymized data which takes max values, then combine the results. 

      So my question was more to help my understanding of whether there is a way of conditionally applying a function during aggregation of data, ie IF <condition> THEN <sum values> ELSE <take max value> 

      Still trying to figure out the concepts in Power BI.

      Thanks again

      Andrew

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous yes you can do conditional measure, here is an idea

         

        Conditional Measure = 
SWITCH ( SELECTEDVALUE ( Table[WhatMeasure] ),
"SUM", SUM ( Table]Column] ),
"MAX", MAX ( Tab;e[Column2] ),
"COUNT", COUNTROWS ( Table )
)

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

        ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡