Forum Discussion

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago
Solved

New Measures, Columns - Data sets.

I think I'm not quite understanding how all this works as I'm stuck again :(  This explanation will be a bit lengthy - sorry, but its complicated what I need - and I cannot provide my data due to confidentiality.

 

I have multiple tables per month. The data I'm using is a snapshot essentially of about 90K records. Each month, all 90K records have to be pulled. It is based on configuration items in a cmdb. 

They want various metrics based on the CI data - and they want monthly running totals in a total. So a column of December Data, January Data, February Data etc.  I did the first month, where I had to create new measures to do calculations. 

 

Trying to make a line chart with this data, where the x-axis is the month, and the Y-Axis is either total, or percentage, and the data point for this one chart is "# of config items without relationships"


The challenge is that the Config Item records are a complete list of all of them 90K+. I added a column to the first table "Reporting Month", so that I have a way to differentiate between Data pulled for December, and data pulled for January. 

 

I created a new measure for the first December dataset to subtract # of Items in one table from the # of items in another set. Worked great.

 

I then went to do January. Went to create a new measure, and it says a measure already exists. I didn't realize I couldn't create a new measure of the same name for multiple tables!  I can't create a new column, because it isn't a column of data - I just need the one number - and a new number for each month. 

 

I've seen lots of videos on how to do power bi, but almost all are based on revenue etc - and things are done differently when trying to work out for IPC data. 

 

What is the best way to handle this? Is there a video someone can point me to that has similar examples? Most of my metric requirements work like this. 

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Lenihan

     

    Create a master calendar table and measures.

     

    Then as you keep adding data the master calendar table will be updated for the new dates in the data.

     

    All the measures can then be filtered for months, quarter, year. 

     

    You can refer to lot of posts in the community for creating calendar tables.

     

    Cheers

     

    CheenuSing

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Lenihan

     

    Is it possible for you to load the data in xlsx format and upload to One Drive / Dropbox and share the link. Also

     include what kind of output are you expecting.

     

    Cheers

     

    CheenuSing

     

    • Lenihan's avatar
      Lenihan
      Icon for Helper III rankHelper III

      I wish I could provide the data, as I know that would be much easier, but the data is corporate confidential.

      I think though I know what I'm supposed to do with respect to this question. Correct me if I go astray:

      I originally assumed that I had to keep adding new tables of monthly data, and then create new measures for each month of data added. I'm a little further in my book I'm reading now, and if I understand, I should be just merging the new data. As long as I'm adding a column called reporting month, I'll be able to differentiate the old data and the new data. The measures will be valid for ALL the data there, even the new data added.  Is that right then?

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Lenihan

         

        Create a master calendar table and measures.

         

        Then as you keep adding data the master calendar table will be updated for the new dates in the data.

         

        All the measures can then be filtered for months, quarter, year. 

         

        You can refer to lot of posts in the community for creating calendar tables.

         

        Cheers

         

        CheenuSing