Changing aggregation condition based on condition

[andrewbt]

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

[parry2k]

@andrewbt 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.⚡

[andrewbt]

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]

@andrewbt 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.⚡

[andrewbt]

Thanks @parry2k 

I've been playing around with this but failed in my attempts with using an aggregation function within the switch, the error message being that a "single value for column 'ID' couldn't be determined. 

Here's a sample for the source and desired results I'm looking for, also the incorrect code I used.  I can kind of see what's going wrong, but not sure how to reformulate it - any ideas?

Summary = SUMMARIZECOLUMNS('Source'[ID],

"Count",COUNT('Source'[ID]),

"Total",SWITCH('Source'[ID],

"anon",SUM(Source[Score]),

MAX('Source'[ID]))) //If not "anon" MAX is what I need

Thanks

Andrew

[v-deddai1-msft · Accepted solution]

Hi @andrewbt ,

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

[andrewbt]

Yes, that was the solution, many thanks @v-deddai1-msft !