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
Super 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

 

  • TomMartens's avatar
    TomMartens
    Super User
    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

     

     

    • tlee's avatar
      tlee
      Regular Visitor
      6 years ago

      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?

       

      • TomMartens's avatar
        TomMartens
        Super User
        6 years ago

        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