cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Summary SUM and AVERAGE in the same Column

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Super User IV

@ehartanto Sure, something like:

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

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted Super User IV

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

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"))

Proud to be a Super User!

2 REPLIES 2
Highlighted Super User IV

@ehartanto Sure, something like:

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

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted Super User IV

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

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"))  