Forum Discussion

jsha's avatar
jsha
Frequent Visitor
6 years ago

Build histogram

I am trying to create a dynamic histogram based on schedule data. Basically I am trying to reproduce this simplified Excel sheet in Power BI (see picture).

 

PS. The example has 3 activities spread out over 15 days, but the real data from in my activities table has 2100 activities spread out over around 1800 days.

 

I suppose I need (1) a separate datetable, *(2) a measure for the daily values and a (3) measure for the cumulative values in order to produce the Line and clustered column chart needed.

 

Any thoughts on how to create this in Power BI?

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    jsha based on the data shown in the image, you need to unpivot your table in the power query and yes, add data dimension in the model and from there everything will be super easy.

    • jsha's avatar
      jsha
      Frequent Visitor
      6 years ago

      Hello again.

      I need some more information.

       

      I have a table called ACTIVITIES, containing 295 columns and approx. 2500 rows. There are 5-10 columns with data which I intend to use. I have also created a date table called Datetable_day.

       

      Table ACTIVITIES

      AN = Activity Number

      ESA = Early Start Actual

      EF = Early Finish

      DU = Duration

      TSH = Number of manhours on activity

       

      Table Datetable_day

      Date

      MonthInCalendar

      Etc.

       

      Which field should I unpivot? And what do you mean by 'add data dimensions in the model? And further; how do I write the measure for the columns (periodic TSH) and how do I write the measure for the line (cumulative TSH)? 

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi jsha ,

         

        Take your sample data for exampel:

        select all the date columns>unpivot the columns,and you will see:(screenshot just shows part of the data)

        Then create a measure as below to calculate the daily values:

         

        _daily values = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute]))

         

        And a measure for the cumulative values:

         

        cumulative values = SUMX(FILTER(ALL('Table'),'Table'[Attribute]<=SELECTEDVALUE('Table'[Attribute])),'Table'[Value])

         

        Finally you will see:(There may be a decimal point in the value, so when calculating the accumulation ,the result will be a little deviation from your expected output )

        For the related .pbix file,pls click here.

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!