Summary SUM and AVERAGE in the same Column

[ehartanto]

I need help to summarize my data (either sum or average) based on another column. For example with the data below, I would like to average rental rate and margin but sum revenue. Is it possible to do this? Everything is in one column so that I could drill down from multiple categories with each unique metrics. On that note is it possible to only format certain rows based on another column, for example if metric = margin, format in %?

Date              Metric                       Amount
January         Revenue                    100
February       Revenue                    200
January         Rental Rate                 50

February       Rental Rate                 40 

January        Margin                         0.3

February      Margin                         0.2

Thanks in advance!

[Greg_Deckler · Accepted solution]

@ehartanto Sure, something like:

Total Revenue = SUMX(FILTER('Table',[Metric]="Revenue"),[Amount])

Average Margin = AVERAGEX(FILTER('Table',[Metric]="Margin",[Amount])

[amitchandak · Accepted solution]

@ehartanto , In case you want them Unpivot you can Unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Or have measures like

Revenue = calculate(sum(Table[Amount]), filter(Table,Table[Metric] ="Revenue"))
Rental Rate = calculate(sum(Table[Amount]), filter(Table,Table[Metric] ="Rental Rate"))
Margin = calculate(sum(Table[Amount]), filter(Table,Table[Metric] ="Margin"))

[Greg_Deckler · Accepted solution]

@ehartanto Sure, something like:

Total Revenue = SUMX(FILTER('Table',[Metric]="Revenue"),[Amount])

Average Margin = AVERAGEX(FILTER('Table',[Metric]="Margin",[Amount])