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

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!

    • TomMartens's avatar
      TomMartens
      Super User
      6 years ago

      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

      • tlee's avatar
        tlee
        Regular Visitor
        6 years ago

        Great - thank you so much!