Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tlee
Regular Visitor

Help showing aggregate data

Hello,

Thank you in advance for any help with this!

 

I am trying to chart Revenues, Stabilized Revenues and Total Costs over time.  My data is entered into the database as absolute values, not change in values, so each quarter would be the acutal value.  There are 6 different items in total and I am trying to sum them up, however, the visual returns numbers that total all the values of all the quarters combined.  When I switch to see the data as a "Maximum" instead of a "Sum", it only gives the highest value of all of the records, and does not separate out the highest value of the records of each group.  I hope this makes sense.  

 

1.png

 

Thank you,

Tammy

1 ACCEPTED SOLUTION

Hey @tlee ,

 

1st create 4 measures and call them MAX Revenue, and so on, like so:

MAX Revenue = MAX('<tablename>'[Revenue])

Then create 4 additional measures and call them SUM of MAX Revenue (or somehting more suitable) like so:

SUM of MAX Revenue =
SUMX(
	VALUES('<tablename>'[Item])
	, [MAX Revenue]
)

These measures will be used on the line chart.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

Hey @tlee ,

 

assuming your data looks like this:

image.png

You have to create measures that aggregate the items in a way you want like so:

Measure =
SUM('Table (2)'[Item 1]) + SUM('Table (2)'[Item 2]) - SUM('Table (2)'[Item 3])

or create a calculated column like so:

Column = 'Table (2)'[Item 1] + 'Table (2)'[Item 2] - 'Table (2)'[Item 3]

 

Hopefully this provides you with some ideas that help to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 

Hi Tom,

Thank you for helping me with this!  My data looks like the picture below.  I would like to see how the totals are at a certain date.  In order to do this I need the highest number for each item, and then add those numbers up to get the total.  If I choose "Max" instead of "Sum" it only picks up the highest amount of all the items, in this case $900 for revenue and $400 for expenses.  The correct data should be 32+15+450+85+95+900 for Revenue.  Is there a way to do this in PowerBI?  

 

Thank you,

Tammy

 

2.png

Hey @tlee ,

 

1st create 4 measures and call them MAX Revenue, and so on, like so:

MAX Revenue = MAX('<tablename>'[Revenue])

Then create 4 additional measures and call them SUM of MAX Revenue (or somehting more suitable) like so:

SUM of MAX Revenue =
SUMX(
	VALUES('<tablename>'[Item])
	, [MAX Revenue]
)

These measures will be used on the line chart.

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks Tom, I will use this in a pinch.  There are approx ~60 data columns in any given month.  Is there a way to do this quickly for all the data?  Or do I have to make new columns for each?

 

Hey @tlee ,

 

you might consider to unpivot your data columns, but it depends if you are doing other inter column calculations.

Not sure why you are asking about columns as I recommended to create measures.

Nevertheless, there is just a tool called Tabular Editor (https://tabulareditor.github.io/) that will allow to bulk create measures, but as this is an advanced feature, I guess doing it once manually is as swift as learning all the Tabular Editor magic 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks so much Tom, I am a newbie at Power BI so I envision the measures as "another column in an access table" or the like, another piece of info to keep track of each month.  Is this a correct way of thinking about the measures?  Or do the measures get stored away somewhere in the background?  

 

I think I will keep to your tip and add them manually.

 

Thanks so much!

Hey @tlee ,

 

looking at a table, matrix visual or even a bar chart , a measure can not be distinguished from another, but basically it's always a good idea to imagine a measure being stored somewhere else, even a measure always has a "home" table. But the choosen "home" table in no way effects the working of the measure.

 

There are some advantages about measures, e.g. they do not need memory if they are not used, a column and also a calculated column always consiumes memory, even if it not used in any visual.

A downside of a measure is that it can not be used to feed a slicer.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Great - thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.