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
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?