Forum Discussion
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.
Thank you,
Tammy
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
8 Replies
Hey tlee ,
assuming your data looks like this:
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
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
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
