Build histogram

[jsha]

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?

[parry2k]

@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]

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]

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]

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?