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 sp...
Need Help
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!

       

      • jsha's avatar
        jsha
        Frequent Visitor
        6 years ago

        Thanks v-kelly-msft .

        I am not able to reproduce the unpivot producing the Value column (shown to the right in you screenshot). What field did you select and which buttons did you push to generate this?

         

        And also another thing: I have a date-set with about 2000 activities spread out over about 2000 days. This means that the unpivot makes a huge table with about 4 million rows. How will this affect performance?