cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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


Thanks in advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

@ehartanto Sure, something like:

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

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

@ehartanto Sure, something like:

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

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors