Forum Discussion

tlee's avatar
tlee
Regular Visitor
6 years ago
Solved

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 ch...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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

     

     

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
6 years ago

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

tlee's avatar
tlee
Regular Visitor
6 years ago
 

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