Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Custom Summarization - change the summarization expression(Formulae) based on the column type

Hi,

I have a table I need to summarize a table in power using own formulas , 

The table contains two ”Id” columns based on which I need to group by, and for summarizing first I need to check the column type, it can be  Average, sum or count any of these . baes on the Type I need to change the formulae for summarization . The formulas are  

SUM = SUM(VALUE1_AMOUNT)

AVG = dDIVIDE( SUM(VALUE1_AMOUNT) , SUM(VALUE1_WEIGHT) )

COUNT = COUNT(VALUE1_RESULT)

The result has to be in the form of a table. After getting it as a table the columns will be used in a table. And finally it has to filter when using  slicers or filters. Is it possible to achieve it ? 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try a measure like

     

    Switch(max(Table[Value1Type])

    "Sum", [Sum],

    "Avg", [Avg],

    "Count", [Count])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      But I am trying to achieve using GroupBy() or Summarize() dax function , I need the value1 , value2 , value3 columns in one temp table. Is it possible ?  
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous , 

    Please have a try.

    Create a measure.

    Measure=VAR TABLE_=SUMMARIZE(TABLE',TABLE[VALUE1],TABLE[VALUE2],TABLE[VALUE3])
RETURN
COUNT(VALUE1_RESULT)

    If I have misunderstood your meaning , please provide more details with your desired output and your pbix file without privacy inforamtion.

     

    Best Regards

    Community Support Team _ Polly

     

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