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

     

     

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

      • tlee's avatar
        tlee
        Regular Visitor
        6 years ago

        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!